Re: Stuck: how can I disable automatic json array unwrapping?

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(at)gmail(dot)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
>
>
>