2018-01-06 22:23 GMT+01:00 Nikita Glukhov <n.glu...@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. > -- > Nikita Glukhov > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company >