On 07.01.2018 00:33, Pavel Stehule wrote:
2018-01-06 22:23 GMT+01:00 Nikita Glukhov <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 correctpostgres=# 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 supportedLINE 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.
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 correctpostgres=# 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 supportedLINE 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;
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 correctpostgres=# 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 supportedLINE 1: select j @* '$.book[?(@.price==6.00)].title' from test; ^ DETAIL: syntax error, unexpected '?' at or near "?"
pgsql-hackers by date:
Соглашаюсь с условиями обработки персональных данных