WHERE condition to filter JSON data in nested object or array

Question

I have a table in a PostgreSQL database

CREATE TABLE js.orders (
    id serial NOT NULL PRIMARY KEY,
    info json NOT NULL
)

containing 6 rows of JSON data:

select * from js.orders;

returns

 id |                                                info                                                
----+----------------------------------------------------------------------------------------------------
  1 | { "customer": "Kapil", "items": {"product": "Heineken","qty": 6}}
  2 | { "customer": "Satyen", "items": {"product": "Heineken","qty": 18}}
  3 | { "customer": "Rekha", "items": {"product": "Carlsberg","qty": 24}}
  4 | { "customer": "Madhuri", "items": {"product": "Kalyani","qty": 12}}
  5 | { "customer": "Srinivas", "items": {"product": "Kingfisher Strong","qty": 12}}
  6 | { "customer": "Saina", "items": [{"product": "Bira91","qty": 6},{"product": "Kalyani","qty": 6} ]}
(6 rows)

The following 'select' query with a 'where' condition:

SELECT info ->> 'customer' AS customer FROM js.orders WHERE info -> 'items' ->> 'product' = 'Heineken';

returns the correct two rows:

customer 
----------
 Kapil
 Satyen
(2 rows)

However, the following query should return two rows:

SELECT info ->> 'customer' AS customer FROM js.orders WHERE info -> 'items' ->> 'product' = 'Kalyani';

but returns only one row:

    customer     
-----------------
 Madhuri
(1 row)

Obviously, the fact that the row with "customer" : "Sania" has a LIST of "items" instead of a single "items" is causing this problem. How should I modify my query so that the correct number of rows is returned. Or is it that the data has to be reformatted otherwise.

Platform is

'PostgreSQL 14.10 (Ubuntu 14.10-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit'

Convert the JSON data to a proper SQL table, then use standard SQL operators. — Ian Kemp - SO dead by AI greed
– Ian Kemp - SO dead by AI greed, Commented Jan 7, 2024 at 16:31

Erwin Brandstetter · Accepted Answer · 2024-01-08 02:27:29Z

2

Your fist issue is that you store structured data as JSON documents instead of plain columns in a relational design, which would be much more efficient for storage and queries.

Your second issue is that you vary the structure of said JSON documents without need. Some have an object and some an array as value for the key "items". (None have a "list", which does not exist in JSON terminology.) Makes queries more complicated.

While stuck with your unfortunate design, this query with the jsonpath operator @? works, because it processes objects and arrays at the given path in default "lax" mode:

SELECT info ->> 'customer' AS customer
FROM   orders
WHERE  info -> 'items' @? '$.product ? (@ == "Kalyani")';

To only look at objects like your original query:

SELECT id, info ->> 'customer' AS customer
FROM   orders
WHERE  info -> 'items' @? 'strict $.product ? (@ == "Kalyani")';

fiddle

Requires Postgres 12 or later, where the SQL/JSON path language was added. And assumes jsonb as data type, not json. The former is typically preferable anyway.
You might want index support for that. See:

Postgres jsonb query for dynamic values

edited Jan 8, 2024 at 2:27

answered Jan 7, 2024 at 15:42

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Calcutta Over a year ago

I understand that this data can be more elegantly stored in a 3NF normalized table structure with two tables joined by a foreign key. It can also be just as elegantly in NoSQL database like MongoDB. The aim of this (rather simplified) exercise was to explore how PostgreSQL can be used INSTEAD of MongoDB so as to avoid the need for another server software in the environment. Thank You for your assistance.

Erwin Brandstetter Over a year ago

Note that this requires jsonb instead of json.

Collectives™ on Stack Overflow

WHERE condition to filter JSON data in nested object or array

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related