Build a single JSON value from two jsonb columns in two different tables

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.

This should do it:

SELECT jsonb_agg(student) AS students
FROM (
   SELECT jsonb_object_agg((kv).key, (kv).value) AS student
   FROM (
      SELECT ass->>'student_id' AS student_id, "char" 'a' AS tbl, id, jsonb_each(ass) AS kv
      FROM (SELECT jsonb_array_elements(assessment       ) AS ass, id FROM assessment) a

      UNION ALL
      SELECT ass->>'student_id'              ,        'b'       , id, jsonb_each(ass)
      FROM (SELECT jsonb_array_elements(others_assessment) AS ass, id FROM result    ) r
      ORDER BY student_id, tbl, id
      ) sub1
   GROUP  BY student_id
-- ORDER  BY student_id  -- to be explicit
   ) sub2
;

db<>fiddle here

In the inner subquery sub1, for each involved table, unnest the top-level JSON array into its element objects with jsonb_array_elements(), and then expand these objects into into a set of key/value pairs. Then UNION 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 type kv - with well-known fields key and value defined by the function.

In the next subquery sub2, aggregate per student_id to unite all attributes with jsonb_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 use LATERAL subqueries ... See:

• How to avoid multiple function evals with the (func()).* syntax in an SQL query?

In the outer SELECT, aggregate all students into a jsonb array with jsonb_agg(). Voilá.
(ORDER BY student_id is carried over from the inner subselect, but add it if you want to be explicit.

StackExchange Database Administrators Q#251233, answer score: 2