> On 18 Jun 2022, at 2:14, Bryn Llewellyn <bryn@yugabyte.com> wrote:
>
> I implemented two complementary functions:
>
> —"no_null_keys()" checks that a "jsonb" value has no occurrences of « "some key": null »
>
> —"strip_null_keys()" removes « "some key": null » occurrences from a "jsonb" value
>
> The code checks with "no_null_keys()" that, as expected, no ingested JSON document has an occurrence of « "some key":
null».
>
> And it uses "strip_null_keys()" on the output of "to_jsonb()" — and, as appropriate, any other built-in JSON function
thatproduces a "jsonb" value.
>
> It was straightforward to implement these two functions by using REGEXP built-in functionality on the canonically
formatted"text" value produced by the "jsonb::text" typecast.
In my experience, using regular expressions applied to document formats tends to get you false positives. I’d be
worriedabout books with titles similar to 'How we wrote a regular expression to detect occurrences of "some key": null
inour JSON documents', for example.
For stripping those null occurrences, you are aware of the json_strip_nulls(json) and jsonb_strip_nulls(jsonb)
functions,right?
For detecting them on a recent PG, the @? operator or json_path_exists(json, jsonpath) functions would probably do the
trick.
I am not too familiar with JSONPATH expressions, but I expect (it passed some preliminary testing) this would detect
yournulls just fine, while taking JSON semantics into account:
jsonb_path_exists(document, '$.** ? (@ == null)'::jsonpath)
For PG-specifics on JSONPATH, see section 9.16.2 on:
https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-OP-TABLE
A recursive query is another possible solution. It would probably perform far worse, but I find them more rewarding to
write.Some people prefer Sodoku.
Regards,
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.