Re: [HACKERS] SQL/JSON in PostgreSQL

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Nikita Glukhov <n(dot)gluhov(at)postgrespro(dot)ru>
Cc: Oleg Bartunov <obartunov(at)gmail(dot)com>, Andrew Dunstan <andrew(dot)dunstan(at)2ndquadrant(dot)com>, Michael Paquier <michael(dot)paquier(at)gmail(dot)com>, Piotr Stefaniak <email(at)piotr-stefaniak(dot)me>, Pgsql Hackers <pgsql-hackers(at)postgresql(dot)org>, Teodor Sigaev <teodor(at)postgrespro(dot)ru>, Alexander Korotkov <a(dot)korotkov(at)postgrespro(dot)ru>, andrew Dunstan <andrew(at)dunslane(dot)net>
Subject: Re: [HACKERS] SQL/JSON in PostgreSQL
Date: 2018-01-06 21:22:06
Message-ID: CAFj8pRDRSpoP009yYrVSuKWBDDiEZHQvc+fwOCFhZQ0_zynGxw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

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 "?"

Regards

Pavel

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Nikita Glukhov 2018-01-06 21:23:32 Re: [HACKERS] SQL/JSON in PostgreSQL
Previous Message Pavel Stehule 2018-01-06 21:11:54 Re: [HACKERS] SQL/JSON in PostgreSQL