CODEWITHSUNDEEP
sqljsonpostgresql
Avocado
Avocado

Reputation: 901

Cross Reference Table to Single Object

In postgres 12, I have a table that stores relationships between parent and child ids. I would like a query that returns a single object that has parent ids as keys, and arrays of child ids as properties.

So far, the best I have gotten is an array of objects, each with a single key that maps to an array of child ids:

Fiddle: https://dbfiddle.uk/?rdbms=postgres_12&fiddle=1854482bbfc442a24bc34b54bf5481cf

Is there some way in pg to "merge" this array of objects into a single object? (or otherwise return a single object) e.g.,

{ 1: [3], 2: [4, 5] }

Upvotes: 2

Views: 147

Answers (1)

Abelisto
Abelisto

Reputation: 15624

Build the JSON object at the final stage:

SELECT
  json_object_agg(parent_id,  z.relationship) AS relationships
FROM (
  SELECT
    parent_id, array_agg(child_id) AS relationship
  FROM
    parent_child_xref
  GROUP BY
    parent_id
) z

fiddle

Upvotes: 3

Related Questions