I have two tables. Both have a jsonb
column with an array of objects, each containing the key student_id
.
I am trying to build one aggregated JSON value from these jsonb columns.
Lets say table A has four rows and each has jsonb
data like:
[
{
"student_id: 1,
"others element from table A" ...
...
},
{
"student_id: 2,
"others element from table A" ...
...
}
...
]
And table B has holds jsonb
values of the form:
[
{
"student_id": 1,
"others element from table B" ...
},
{
"student_id": 2,
"others element from table B" ...
}
...
]
There is only one row from table B at a time that is to be mapped to multiple rows from table A, all containing different "other_elements"
but with the same "student_id"
value for each object.
My expect result is of the form:
[
{
"student_id": 1,
"others elements from table A" ...
"others elements from table B" ...
},
{
"student_id": 2,
"others elements from table A" ...
"others elements from table B" ...
}
]
Here is a fiddle with sample data demonstrating my case.
Best Answer
This should do it:
db<>fiddle here
In the inner subquery
sub1
, for each involved table, unnest the top-level JSON array into its element objects withjsonb_array_elements()
, and then expand these objects into into a set of key/value pairs. ThenUNION ALL
all of them together and (optionally) order rows, as the order bears significance in the next step.Note how
jsonb_each()
is called once, returning two fields, which we carry over as one composite typekv
- with well-known fieldskey
andvalue
defined by the function.In the next subquery
sub2
, aggregate perstudent_id
to unite all attributes withjsonb_object_agg()
. Values from later rows overrule earlier rows in case of key conflicts.Fields of the composite type are accessed with
(kv).key, (kv).value
. This way, the function is only executed once (not once per field). Alternatively useLATERAL
subqueries ... See:In the outer
SELECT
, aggregate all students into ajsonb
array withjsonb_agg()
. Voilá. (ORDER BY student_id
is carried over from the inner subselect, but add it if you want to be explicit.