Stuck: how can I disable automatic json array unwrapping?

From: Seref Arikan <serefarikan(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Stuck: how can I disable automatic json array unwrapping?
Date: 2021-10-15 17:50:49
Message-ID: CA+4Thdo+i=SDdFotPnY-Q7gOSkgwXYUfAuSsOtmoXVUTgac6vg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Ron 2021-10-15 18:16:32 Re: "two time periods with only an endpoint in common do not overlap" ???
Previous Message Tom Lane 2021-10-15 17:08:14 Re: PostgreSQL 14: pg_dump / pg_restore error: could not write to the communication channel: Broken pipe