[Solved] Storing JSON Array of Arrays in PostgreSql

Question

First statement returns two rows, one for each array.

    select jsonb_array_elements(msg->'root') as el
    from js

Then returns each individual values as text.

with aa as
(
    select jsonb_array_elements(msg->'root') as el
    from js
)
select jsonb_array_elements(el)->>'cid' as cid,
       jsonb_array_elements(el)->>'Display' as Display,
       jsonb_array_elements(el)->>'FName' as FName,
       jsonb_array_elements(el)->>'LName' as LName         
from aa;

This returns 3rd element of 2nd array

select (ARRAY(select * from jsonb_array_elements(A1.Elements)))[3] Elem
from
    (select (ARRAY(select * from jsonb_array_elements(msg->'root')))[2] Elements
    from js) A1;

Below value is according data I’ve used in my resxtester project. Maybe does not match with your json text.

{“cid”: “CID2”, “FName”: “User”, “LName”: “Three”, “Display”: “User
Three”}

Check it here: http://rextester.com/IAU74251

Update

Filter by some of the field of the first array:

select * from                
(select jsonb_array_elements(msg->'root'->0) as a from js) elem
where elem.a->>'Display' like '%Two';

Accepted Answer