CODEWITHSUNDEEP
postgresqljsonb
Sake
Sake

Reputation: 4123

How to transform jsonb array of array into records

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] |

Upvotes: 1

Views: 81

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175586

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

Upvotes: 2

Related Questions