json_array_elements returns empty dataset in postgres

Question

I was trying to read elements from Json array inside another array that is stored on Postgres database, but my result set was always empty. My table looks like below: enter image description here

Sample Json from table:

{"test":{"accountName":"OS1U","details":[{"status":{"statusCode":"Test Ready","modifiedDate":"2023年07月31日T14:53:49Z"},"products":[{"productId":"0001","productName":"2GB","productDescription":"testproduct12"},{"productId":"0005","productName":"2GB","productDescription":"testproduct12"}]}]}}

Here my requirement is to get all the id's from table which is having productId (0001,0005). I tried below sql:

SELECT x.id 
FROM (
 SELECT json_array_elements("json" -> 'test' -> 'details'->'products') AS products,"id"
 FROM table_test b
 
 ) x
WHERE x.products ->> 'productId' in ('0001','0005');

But it always returns empty dataset. Can anyone please guide me to correct the above statement

Answer 1

The problem is that both details and products are arrays.

If you store it as jsonb then you can use jsonb_path_exists much more efficiently

SELECT t.id
FROM table_test t
WHERE jsonb_path_exists(t.json, '
 $.test.details[*].products[*].productId ? (@ == "0001" || @ == "0005")
 ');

db<>fiddle

Otherwise you need to do a series of json_array_element calls to unwrap each array.

SELECT t.id
FROM table_test t
WHERE EXISTS (SELECT 1
 FROM json_array_elements(t.json -> 'test' -> 'details') j1
 CROSS JOIN json_array_elements(j1 -> 'products') j2
 WHERE j2 ->> 'productId' IN ('0001', '0005')
);

db<>fiddle

• Karthika: Thanks , It works now