As always, the solution is in the postgresql documentation. Using exists() from a filter prior to accessing an optional field solves my problem. I can have both strict mode and access to optional fields without getting an error this way.
On Fri, Oct 15, 2021 at 6:50 PM Seref Arikan <serefari...@gmail.com> wrote: > I have json data which is based on arbitrary levels of hierarchy. The json > objects I need to query are at an unknown depth in the document, which > means I have to use the recursive wildcard member accessor (.**) > > The problem is, the path to these unknown depths also contain arrays, and > when .** accessor encounters them, they're automatically unwrapped, so I > end up with duplicate results for the same json object in data. My > understanding of the mechanics may be incomplete but basically it is the > situation explained here at the end of 9.16.2 here, just before 9.16.2.1 > https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-SQLJSON-PATH-OPERATORS > > Using the strict mode solves my problem, but then I have another one: the > data model producing this json model has optional fields, so it is > perfectly OK for json data not to have some fields, and queries to return > empty results in this case. In strict mode, using these optional fields > results in an error. > > So I have duplicate data if I use .** (which I must), and errors if I use > lax mode for json structure (which I must). > > Is there any way I can get strict mode behaviour from .** without using > strict mode? Is there any other way of achieving the behaviour of // > operator from XPath? The JsonPath page here refers to .. operator for > JsonPath, which corresponds to .** as far as I can see. Am I looking at the > wrong feature in postgres's json support? > > Cheers, > Seref > > >
