Flatten the jsonb nested array in postgresql

Question

I have a data in the table as

id(integer)    | label(text)     | value(jsonb)     |
---------------|-----------------|------------------|
12345          |       Education | [[{"label": "Type", "value": "Under Graduate"},{"label": "Location", "value": "New Delhi"}],[{"label": "Type", "value": "Post Graduate"}]]|

And the required output is :

id    | label               | value          |
------|---------------------|----------------|
12345 | Education_Type_1    | Under Graduate |
12345 | Education_Location_1| New Delhi      |
12345 | Education_Type_2    | Post Graduate  |

Can someone please help me solve this issue that I am facing?

what is the data type of column "value"?

Vivek S.
– Vivek S.

2018-06-28 06:19:47 +00:00
Commented Jun 28, 2018 at 6:19 — Vivek S.
– Vivek S., Commented Jun 28, 2018 at 6:19
value is of type jsonb.

Kumar Kisalay
– Kumar Kisalay

2018-06-28 06:25:29 +00:00
Commented Jun 28, 2018 at 6:25 — Kumar Kisalay
– Kumar Kisalay, Commented Jun 28, 2018 at 6:25

Fahad Anjum · Accepted Answer · 2018-06-28 07:55:53Z

2

You can use jsonb_array_elements(your_jsonb_column). Tested on Postgres 9.6. You can use json_array_elements(your_json_column) if you are using some other version.

Table:

create table test (id int,label text, value jsonb);

Insert Statement:

insert into test values(12345,'Education','[[{"label": "Type", "value": "Under Graduate"}],[{"label": "Type", "value": "Post Graduate"}]]');
insert into test values(123456,'Education2','[[{"label": "Type2", "value": "Under Graduate2"}],[{"label": "Type2", "value": "Post Graduate2"}]]');

SQL Query:

select id, label,jsonb_array_elements(value)->0->>'value'
from test

Where 0 is used to take first elements from an array. ->> is used to remove quotes from the string.

Output:

id  label   value
12345   Education   Under Graduate
12345   Education   Post Graduate
123456  Education2  Under Graduate2
123456  Education2  Post Graduate2

SQL Fiddle

edited Jun 28, 2018 at 7:55

answered Jun 28, 2018 at 6:40

Fahad Anjum

1,2561 gold badge10 silver badges19 bronze badges

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

7 Comments

Kumar Kisalay Over a year ago

Yes, we can do this but can we use recursive CTE in order to iterate through each element and then print its output in the desired format?

Fahad Anjum Over a year ago

Why do you want to use recursive CTE if Postgres is already providing you the same behavior using the function?

Kumar Kisalay Over a year ago

Yes, but how do I iterate over each element in an array. jsonb_array_elements(value)->0->>'value' will only give me the value for the first item in the array.

Fahad Anjum Over a year ago

@KumarKisalay updated answer with output. please check the SQL fiddle link. Have you tried the above solution?

Kumar Kisalay Over a year ago

It will only work if I have only one object inside the nested array. If my array is of type: [[{"label":"Type", "value": "Under Graduate"},{"label":"Type", "value": "Under Graduate"}],[{"label":"Type", "value": "Post Graduate"}],[{"label":"Type", "value": "Some education 1"}]], Then this query will not give the exact output.

|

Kumar Kisalay · Accepted Answer · 2018-06-28 10:56:35Z

0

I found the solution. Thanks @Fahad Anjum. I wrote the solution on top of your soultion.

SELECT 
    'Education_' || (jsonb_array_elements(elem)->>'label')::text || '_' || pos::text AS label, jsonb_array_elements(elem)->>'value'
FROM jsonb_array_elements(
    '{"test": [
        [{"label":"Type", "value": "Under Graduate"},{"label":"Location", "value": "New Delhi"},{"label":"CGPA", "value": "9.07"}],
        [{"label":"Type", "value": "Post Graduate"},{"label":"Location", "value": "Bangalore"}],
        [{"label":"Type", "value": "Some education 1"}]]}'::jsonb->'test'
    ) WITH ordinality arr(elem, pos);

answered Jun 28, 2018 at 10:56

Kumar Kisalay

732 silver badges7 bronze badges

Comments

Hosam Aly · Accepted Answer · 2019-12-06 13:36:22Z

-1

Since we value column is like multi-dimensional array of irregular dimension we will use recursive query to find solution.

Below query result in required output you want

I have populated your sample data in CTE.

with recursive cte(id,label,value,dims) as (
 select
  12345,
  'Education'::text,
  '[
    [
     {"label": "Type", "value": "Under Graduate"},
     {"label": "Location", "value":"New Delhi"}
    ],
    [
     {"label": "Type", "value": "Post Graduate"}
    ]
   ]'::jsonb,
  jsonb_array_length('[[{"label": "Type", "value": "Under Graduate"},{"label": "Location", "value": "New Delhi"}],[{"label": "Type", "value": "Post Graduate"}]]'::jsonb)
), res(id,label,val,dims) as (
    select cte.id,cte.label,l.v,cte.dims-1
    from cte,lateral(
        select jsonb_array_elements(cte.value) as v
    ) l
    union all
    select
      res.id,res.label,l.v,res.dims-1
    from res,lateral(
       select jsonb_array_elements(res.val) as v
     ) l
    where
     res.dims>0
)
select
    res.id,
    res.val->>'value' as value,
    res.label ||
    '_'||
    (res.val->>'label')::text ||
    '_' ||
    row_number() over (partition by id,label,(res.val->>'label')::text) as label
from res
where dims=0

edited Dec 6, 2019 at 13:36

Hosam Aly

42.6k38 gold badges147 silver badges183 bronze badges

answered Jun 28, 2018 at 11:31

Sabari

2442 silver badges6 bronze badges

1 Comment

Kumar Kisalay Over a year ago

@Sabri, Thanks for the solution. It Worked.

Collectives™ on Stack Overflow

Flatten the jsonb nested array in postgresql

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related