Re: [HACKERS] SQL/JSON in PostgreSQL

2018-01-06 22:23 GMT+01:00 Nikita Glukhov <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.

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