How to transform jsonb array of array into records

Question

Given an array of objects, we can turn that into recordset very easily with jsonb_to_recordset.

select * from jsonb_to_recordset($$[
  {"name": "name01", "age": 12},
  {"name": "name02", "age": 14},
  {"name": "name03", "age": 16},
  {"name": "name04", "age": 18}
]$$) as (name text, age int)


name   |age |
-------|----|
name01 |12  |
name02 |14  |
name03 |16  |
name04 |18  |

But what can we do if we prefer source data in the form of array of array ? How to transform below query to yield similar result to the above ?

select array['name', 'age']  "labels"
     , x.value               "values" 
from jsonb_array_elements($$[
  ["name01", 12],
  ["name02", 14],
  ["name03", 16],
  ["name04", 18]
]$$) x


labels     |values         |
-----------|---------------|
{name,age} |["name01", 12] |
{name,age} |["name02", 14] |
{name,age} |["name03", 16] |
{name,age} |["name04", 18] |

Lukasz Szozda · Accepted Answer · 2018-05-03 13:07:16Z

2

You could use ->>:

select x.value->>0 AS name,
       x.value->>1 AS age     
from jsonb_array_elements($$[
  ["name01", 12],
  ["name02", 14],
  ["name03", 16],
  ["name04", 18]
]$$) x;

Output:

+--------+-----+
|  name  | age |
+--------+-----+
| name01 |  12 |
| name02 |  14 |
| name03 |  16 |
| name04 |  18 |
+--------+-----+

DBFiddle Demo

answered May 3, 2018 at 13:07

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

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

1 Comment

Sake Over a year ago

I was seriously over thinking. :)

Collectives™ on Stack Overflow

How to transform jsonb array of array into records

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related