CODEWITHSUNDEEP
sqljsonpostgresqlnested
tenet testuser1
tenet testuser1

Reputation: 109

Query individual values in a nested json record

I have the following table in Aurora Postgres:

create table newtable(column1 jsonb);

insert into newtable(column1) values 
('{"usertype":[{"type":"staff","status":"active","permissions": {"1": "add user","2": "add account"}}
             , {"type":"customer","status":"suspended"}
             , {"type":"corporate","status":"active"}]}');

I can get to a certain degree of data selection using the LATERAL query syntax. But I need help with querying one level deeper.

PLEASE Ignore: the additional rows selected as I tried a few formats.

SELECT json_line_item ->> 'type' as "type", json_line_item ->> 'status' as "status" , json_line_item->>'permissions'  "permissions"
FROM   newtable
,  LATERAL jsonb_array_elements(newtable.column1 -> 'usertype') json_line_item;

I want the permissions column as a list without any extra double quotes.

Another option is to get one single permissions by filtering (for eg: permissions[1] or permissions[2] etc.

Upvotes: 1

Views: 1010

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656754

List

I want the permissions column as a list without any extra double quotes.

Use jsonb_each_text() in another LATERAL subquery to extract (key and) object values, then concatenate the list with string_agg():

SELECT id
     , js_line_item ->> 'type'        AS type
     , js_line_item ->> 'status'      AS status
     , js_line_item ->> 'permissions' AS permissions
     , p.permission_list                                -- !
FROM   newtable n
LEFT   JOIN LATERAL jsonb_array_elements(n.column1 -> 'usertype') AS js_line_item ON true
CROSS  JOIN LATERAL (
   SELECT string_agg(value, ', ') AS permission_list
   FROM   jsonb_each_text(js_line_item -> 'permissions')
   ) p;

db<>fiddle here

Use LEFT JOIN LATERAL to retain all rows even without "usertype" data. I added an id to make that clearer. CROSS JOIN like before would eliminate such rows from the result.

The added table expression can be joined with a CROSS JOIN because a subquery with an aggregate function and no GROUP BY always returns a single aggregated row.

Single permissions

... get one single permissions by filtering (for eg: permissions[1] or permissions[2] etc.

SELECT id
     , js_line_item ->> 'type'   AS type
     , js_line_item ->> 'status' AS status
     , js_line_item -> 'permissions' ->> '1' AS permission_1  -- !
     , js_line_item #>> '{permissions, 2}'   AS permission_2  -- !
--   , js_line_item['permissions']['3']      AS permission_3  -- ! ① for pg 14
FROM   newtable n
LEFT   JOIN LATERAL jsonb_array_elements(n.column1 -> 'usertype') AS js_line_item ON true;

db<>fiddle here

In (current) Postgres 13 you cannot iterate through objects of a record with subscripts like permissions[1]. That only works for arrays.

I added two syntax variants for Postgres 13.

① The third syntax variant requires Postgres 14, using the new "jsonb subscripting".
Use quoted key names to extract values from objects, and ordinal integer positions (starting at 0) to extract elements from arrays.
3 in our example js_line_item['permissions']['3'] is a key name and has to be quoted.

The manual:

If a jsonb value is an array, numeric subscripts start at zero, and negative integers count backwards from the last element of the array. Slice expressions are not supported.

Upvotes: 1

Related Questions