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   value12345   Education   Under Graduate12345   Education   Post Graduate123456  Education2  Under Graduate2123456  Education2  Post Graduate2

SQL Fiddle

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);

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 labelfrom reswhere dims=0

• sql
• database
• postgresql