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
