CODEWITHSUNDEEP
jsonpostgresql
desmond
desmond

Reputation: 2081

How to query nested array of jsonb

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:

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

Upvotes: 1

Views: 1156

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176144

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

Upvotes: 1

Related Questions