Merging an array of JSON objects to one JSON column in Postgres

Question

I have two tables, products and products_ext that can be reduced essentially to this basic form:

CREATE TABLE "products" (
  "product_id" TEXT PRIMARY KEY
);

CREATE TABLE "products_ext" (
  "product_id" TEXT NOT NULL,
  "key" TEXT NOT NULL,
  "value" JSON,
  PRIMARY KEY ("product_id", "key"),
  FOREIGN KEY ("product_id") REFERENCES "products"("product_id")
    ON DELETE CASCADE
    ON UPDATE CASCADE
  );

Let us assume mock data

INSERT INTO "products" ("product_id") VALUES
  ('test1'),
  ('test2'),
  ('test3');

INSERT INTO "products_ext" (product_id, "key", "value") VALUES
  ('test1', 'foo', '"Foo"'),
  ('test1', 'bar', '"Bar"'),
  ('test2', 'foo', '"Foo"');

I can use a query

SELECT
  "P"."product_id",
  ARRAY(
    SELECT
      json_build_object(
        "E"."key",
        "E"."value"
      )
    FROM "products_ext" AS "E"
    WHERE "E"."product_id" = "P"."product_id"
  )
FROM
  "products" AS "P";

which yields

product_id |                     array                     
------------+-----------------------------------------------
test1      | {"{\"foo\" : \"Foo\"}","{\"bar\" : \"Bar\"}"}
test2      | {"{\"foo\" : \"Foo\"}"}

but I cannot make it to yield a merged JSON. Is there an easy way in Postgres 10 to merge an array of multiple JSONs as one JSON that would yield?

product_id |                   json                
------------+----------------------------------------
test1      | {\"foo\" : \"Foo\", \"bar\" : \"Bar\"}
test2      | {\"foo\" : \"Foo\"}
test3      | {}

Primary key pair "product_id" and "key" already make sure that there are no key collisions. There may be rows in the products that do not have any data in products_ext and in those cases an empty JSON object should be provided.

S-Man · Accepted Answer · 2019-08-15T07:08:04.337

2

demo:db<>fiddle

Use json_object_agg():

SELECT
  p.product_id AS product_id,
  json_object_agg(e.key, e.value)
FROM
  products AS p
JOIN
  products_ext AS e ON p.product_id = e.product_id
GROUP BY p.product_id;

Edit for empty product_ext values:

demo:db<>fiddle

SELECT
  p.product_id AS product_id,
  COALESCE(
      json_object_agg(e.key, e.value) FILTER (WHERE e.key IS NOT NULL),
      '{}'
  )
FROM
  products AS p
LEFT JOIN
  products_ext AS e ON p.product_id = e.product_id
GROUP BY p.product_id;

edited Aug 15 '19 at 07:08

answered Aug 14 '19 at 13:55

S-Man

22,521
7
40
63

close enough, but I cannot use this straight, because there are a multitude of other columns in `products`. You did lead me to the correct solution and I will gladly accept your answer if you modify it to match this fiddle I made from yours: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=455ea598954ab447a8edda4bce167c0a – adrenalin Aug 14 '19 at 14:16
Sorry, I did not understand: Is there something wrong with your fiddle (don't see anything) or do you want me to change the answer using your code? – S-Man Aug 14 '19 at 14:30
Hi! Sorry for being unclear. Your original answer lead me to the correct answer, which is presented in my own fiddle. I cannot use `products_ext` as the only table as the main query will have to use `products` (there simply is more data to fetch from it), but need to aggregate as JSON the data from `products_ext` so that it populates one crucial column of the result set, but doesn't define it as a whole. – adrenalin Aug 15 '19 at 06:40
1

Ok, changed the query :) – S-Man Aug 15 '19 at 06:45
...and one more thing: this query *still* (my bad) omits the rows that don't have extensions, so your original query can be used as a subquery only, since - as I provided in my fiddle - does practically an inner join. So back to the drawing board... This new fiddle takes into account the special case of missing ext data I hadn't defined initially (will update the question): https://dbfiddle.uk/?rdbms=postgres_10&fiddle=387ade17b3a953c90e4b32ff4a598129 - your answer is used as the subquery seed, so if you want to update it once again I will accept it. Sorry for the confusion! – adrenalin Aug 15 '19 at 06:53
1

Added a solution with left join which is much faster then yours. Please update your question to match the answer as well – S-Man Aug 15 '19 at 07:09

Merging an array of JSON objects to one JSON column in Postgres

1 Answers1