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
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 |