Re: [HACKERS] SQL/JSON in PostgreSQL

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;

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