Postgresql - How to query nested array elements

Question

I have JSON array data in PostgreSQL 13 table. I want to query this table to see all the nested array data in the output. I tried the below query, but it's not giving the expected output.

select 
json_data::json -> 'Rows' -> 0 -> 'Values' ->> 0 as Lid
,json_data::json -> 'Rows' -> 0 -> 'Values' ->> 1 as L2LicenseId
,json_data::json -> 'Rows' -> 1 -> 'Values' ->> 0 as Lid
,json_data::json -> 'Rows' -> 1 -> 'Values' ->> 1 as L2LicenseId
from test;

Can someone please help me?

Sample Data

CREATE TABLE IF NOT EXISTS test
(
    json_data text 
);

INSERT INTO test (json_data) VALUES ('{"Origin":"api","Topic":"licenses","Timestamp":"2023-02-07T12:46:42.2568898+00:00","Columns":["LId","L2LicenseId","SfdcAccountId","SfdcLineItemId","SL","Quantity","StartDate","EndDate","DisplayName","ProductPrimaryKey"],"Schema":["string","string","string","string","string","int32","datetime","datetime","string","string"],"Rows":[{"Values":["1234","123456","ACC_","PurchaseT","SKU-0000","1","2023-01-09T00:00:00.0000000","2024-01-08T00:00:00.0000000","Automation with 5 users","lc11dev.my-dev.com"]},{"Values":["8967","8967-e567","fihikelo","Addon_00000490_2nd_GB","SKU-0490","3","2023-01-01T00:00:00.0000000","2023-01-22T00:00:00.0000000","Automation, Data 5GB","mygreattest01311433.my-dev.com"]}]}')

Expected Output

DB FIDDLE

@Albina No! I have just shared 2 Sample rows whereas in actual there can be n number of Values. However, each of the array will have 9 fields (elements) — Vikas J, Feb 08 '23 at 13:53

score 1 · Answer 1 · answered Feb 08 '23 at 17:18

You can do it using PostgreSQL function jsonb_array_elements (or json_array_elements). This function extracts all Json array elements like as rows view.

select 
    a2.value -> 'Values' ->> 0 as Lid, 
    a2.value -> 'Values' ->> 1 as L2LicenseId
from test a1
cross join jsonb_array_elements(a1.json_data::jsonb->'Rows') a2

-- Result: 
lid  | l2licenseid |
--- -+-------------+
1234 | 123456      |
8967 | 8967-e567   |

Postgresql - How to query nested array elements

1 Answers1