Thread: Stuck: how can I disable automatic json array unwrapping?
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
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 <serefarikan@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-OPERATORSUsing 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