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.gluhov@postgrespro.ru 
> <mailto:n.gluhov@postgrespro.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