How to query nested array of jsonb

Question

I am working on a PostgreSQL 11 table with a column of nested and multiple jsonb objects

to simulate the issue: -

CREATE TABLE public.test
(
  id integer NOT NULL DEFAULT nextval('test_id_seq'::regclass),
  testcol jsonb
)
insert into test (testcol) values 
('[{"type": {"value": 1, "displayName": "flag1"}, "value": "10"},
{"type": {"value": 2, "displayName": "flag2"}, "value": "20"}, 
{"type": {"value": 3, "displayName": "flag3"}, "value": "30"},
 {"type": {"value": 4, "displayName": "flag4"}},
 {"type": {"value": 4, "displayName": "flag4"}},
 {"type": {"value": 6, "displayName": "flag6"}, "value": "40"}]');

I am trying to:

get outer value if type= specific value. e.g. get the value 30, if flag3 is in displayname.
count occurrence of flag4 in inner json

because there are two "value" elements, it is not quite clear to me what you are expecting. please add the expected result — S-Man
– S-Man, Commented Oct 9, 2019 at 15:07

Lukasz Szozda · Accepted Answer · 2019-10-09 20:28:49Z

1

You could use json_to_recordset to parse it:

WITH cte AS (
  SELECT  test.id, sub."type"->'value' AS t_value, sub."type"->'displayName' AS t_name, value
  FROM test
  ,LATERAL jsonb_to_recordset(testcol) sub("type" jsonb, "value" int)
)
SELECT *
FROM cte
-- WHERE ...
-- GROUP BY ...;

db<>fiddle demo

edited Oct 9, 2019 at 20:28

answered Oct 9, 2019 at 15:17

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

5 Comments

desmond Over a year ago

hi, i tried with command - 'WITH cte AS ( SELECT test.id, sub."type"->'value' AS t_value, sub."type"->'displayName' AS t_name, value FROM test ,LATERAL json_to_recordset(testcol::json) sub("type" jsonb, "value" text) ) SELECT t_name, value FROM cte where t_name::text='flag3';' but it didnt manage to get flag3, which part i did wrong?

Bergi Over a year ago

Don't cast to json, use jsonb_to_recordset straight away

Lukasz Szozda Over a year ago

@desmond dbfiddle.uk/…

desmond Over a year ago

it works thanks. but another question, because i have to work with a lot of rows (a row with a jsonb column with 1 to many objects), will this method cause performance issue?

Lukasz Szozda Over a year ago

@desmond It depends on too many things. Normally we should treat data in columns as atomic. If this solution starts perfroming badly you could index/normalize data.

Collectives™ on Stack Overflow

How to query nested array of jsonb

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related