I have got following jsonb column in a table with example values:
{
"persons": [
{
"person": [
{"id": "1", "name": "John"}
]
},
{
"person": [
{"id": "2", "name": "Oscar"}
]
}
]
}
Is there a way to create a row value with 1-John, 2-Oscar? Suppose that there is not fixed number of persons for each row.
I tried several functions and crossjoins but nothing worked.
As you have two nested arrays, you need to unnest them twice:
with data (doc) as (
values ('{
"persons":
[
{"person": [{"id": "1", "name": "John"}]},
{"person": [{"id": "2", "name": "Oscar"}]}
]}'::jsonb)
)
select t2.p ->> 'id' as id,
t2.p ->> 'name' as name
from data,
jsonb_array_elements(doc -> 'persons') as t1(p),
jsonb_array_elements(t1.p -> 'person') as t2(p);
returns:
id | name
---+------
1 | John
2 | Oscar
To concatenate that into a single row, use string_agg():
with (...)
select string_agg(concat_ws('-', t2.p ->> 'id', t2.p ->> 'name'), ', ')
from ...
returns:
string_agg
---------------
1-John, 2-Oscar
