Re: [HACKERS] SQL/JSON in PostgreSQL

On 07.01.2018 00:11, Pavel Stehule wrote:

> 2018-01-06 22:02 GMT+01:00 Oleg Bartunov <obartunov@gmail.com 
> <mailto:obartunov@gmail.com>>:
>
>     On Sat, Jan 6, 2018 at 8:22 AM, Pavel Stehule
>     <pavel.stehule@gmail.com <mailto:pavel.stehule@gmail.com>> wrote:
>     > Hi
>     >
>     > I am checking the JSONPath related code
>     >
>     > Questions, notes:
>     >
>     > 1. jsonpath operators are not consistent with any other .. json,
>     xml .. I am
>     > missing ?, @> operátors
>
>     I have slides about jsonpath
>     http://www.sai.msu.su/~megera/postgres/talks/sqljson-pgconf.eu-2017.pdf
>     <http://www.sai.msu.su/%7Emegera/postgres/talks/sqljson-pgconf.eu-2017.pdf>
>
>     > 2. documentation issue - there is "'{"a":[1,2,3,4,5]}'::json *?
>     '$.a[*] ? (@
>     >> 2)'" - operator *? doesn't exists
>
>     There are should be @? operator
>
>     > 3. operator @~ looks like too aggressive shortcut - should be better
>     > commented
>     >
>     > What is not clean, if jsonpath should to create some new
>     operators for json,
>     > jsonb types? It is special filter, defined by type, so from my
>     perspective
>     > the special operators are not necessary.
>
>     It's impossible to distinguish jsonpath from text, so introducing
>     new operators
>     are easier than everytime explicitly specify jsonpath datatype.
>
>
> There are two possible solutions - special operator or explicit 
> casting. In this case I am not sure if special operator for this case 
> is good solution. Probably nobody will use it - because there SQL/JSON 
> functions, but I don't think so this inconsistency is correct.
>
> I have not strong opinion about it - it will be hidden feature for 
> almost all users.
>
Operators are necessary for index support now.

Operators allows us to use a more concise syntax in simple cases, when 
we extract JSON item(s) without error handling:
js @* '$.key'
vs
JSON_QUERY(js, '$.key' RETURNING jsonb ERROR ON ERROR)


Also @* оperator gives us ability to extract a set of JSON items. 
JSON_QUERY can only wrap extracted item sequence into JSON array which 
we have to unwrap with our json[b]_array_elements() function. I also 
thought about returning setof-types in JSON_VALUE/JSON_QUERY:

JSON_QUERY(jsonb '[1,2,3]', '$[*]' RETURNING SETOF jsonb)

But it is not so easy to implement now, because we should introduce new 
node like TableFunc (or also we can try to use existing JSON_TABLE 
infrastructure).

Set-returning expressions are not allowed in every context, so for 
returning singleton items there should be additional operator.

-- 
Nikita Glukhov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company