Re: [HACKERS] SQL/JSON in PostgreSQL

On 07.01.2018 00:33, Pavel Stehule wrote:

> 2018-01-06 22:23 GMT+01:00 Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru
> <mailto:n(dot)gluhov(at)postgrespro(dot)ru>>:
>
> On 07.01.2018 00:22, Pavel Stehule wrote:
>
>> Hi
>>
>> I try jsonpath on json
>>
>> {
>>     "book":
>>     [
>>         {
>>             "title": "Beginning JSON",
>>             "author": "Ben Smith",
>>             "price": 49.99
>>         },
>>
>>         {
>>             "title": "JSON at Work",
>>             "author": "Tom Marrs",
>>             "price": 29.99
>>         },
>>
>>         {
>>             "title": "Learn JSON in a DAY",
>>             "author": "Acodemy",
>>             "price": 8.99
>>         },
>>
>>         {
>>             "title": "JSON: Questions and Answers",
>>             "author": "George Duckett",
>>             "price": 6.00
>>         }
>>     ],
>>
>>     "price range":
>>     {
>>         "cheap": 10.00,
>>         "medium": 20.00
>>     }
>> }
>>
>>
>> I am not jsonpath expert, so I can be bad
>>
>> How I can get title of book with cost 6?
>>
>> postgres=# select j @* '$.book[*] ? (@.price==6)' from test;
>> ┌─────────────────────────────────────────────────────┐
>> │ ?column?                       │
>> ╞═════════════════════════════════════════════════════╡
>> │ { ↵│
>> │             "title": "JSON: Questions and Answers",↵│
>> │             "author": "George Duckett",            ↵│
>> │             "price": 6.00                          ↵│
>> │         } ↵│
>> │ │
>> └─────────────────────────────────────────────────────┘
>> (1 row)
>>
>> -- not sure, if it is correct
>> postgres=# select j @* '$.book[*].title ? (@.price==6)' from test;
>> ┌──────────┐
>> │ ?column? │
>> ╞══════════╡
>> └──────────┘
>> (0 rows)
>>
>> I found some examples, where the filter has bigger sense, but it
>> is not supported
>>
>>
>> LINE 1: select j @* '$.book[?(@.price==6.00)].title' from test;
>>                     ^
>> DETAIL:  syntax error, unexpected '?' at or near "?"
>>
> ".title" simply should go after the filter:
>
> select j @* '$.book[*] ? (@.price==6.00).title' from test;
>
>
> It is working, thank you.
>
> and the form "$.book[?(@.price==6.00)].title" ? I found this example
> in some other SQL/JSON implementations.
>
This is non-standard feature, but it can be easily added for
compatibility with other implementations.

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