Stuck: how can I disable automatic json array unwrapping?

Поиск
Список
Период
Сортировка
От Seref Arikan
Тема Stuck: how can I disable automatic json array unwrapping?
Дата
Msg-id CA+4Thdo+i=SDdFotPnY-Q7gOSkgwXYUfAuSsOtmoXVUTgac6vg@mail.gmail.com
обсуждение исходный текст
Ответы Re: Stuck: how can I disable automatic json array unwrapping?  (Seref Arikan <serefarikan@gmail.com>)
Список 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


В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: PostgreSQL 14: pg_dump / pg_restore error: could not write to the communication channel: Broken pipe
Следующее
От: Ron
Дата:
Сообщение: Re: "two time periods with only an endpoint in common do not overlap" ???