Postgres Pro
Downloads
Home   >   mailing lists

Re: [HACKERS] SQL/JSON in PostgreSQL - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: [HACKERS] SQL/JSON in PostgreSQL
Date
Msg-id CAFj8pRDRSpoP009yYrVSuKWBDDiEZHQvc+fwOCFhZQ0_zynGxw@mail.gmail.com
Whole thread Raw
In response to Re: [HACKERS] SQL/JSON in PostgreSQL  (Nikita Glukhov <n.gluhov@postgrespro.ru>)
Responses Re: [HACKERS] SQL/JSON in PostgreSQL
List pgsql-hackers
Tree view
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

pgsql-hackers by date:

Previous
From: Pavel Stehule
Date:
Subject: Re: [HACKERS] SQL/JSON in PostgreSQL
Next
From: Nikita Glukhov
Date:
Subject: Re: [HACKERS] SQL/JSON in PostgreSQL