CODEWITHSUNDEEP
postgresqlpostgresql-10
adrenalin
adrenalin

Reputation: 1658

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

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.

Upvotes: 3

Views: 1226

Answers (1)

S-Man
S-Man

Reputation: 23676

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;

Upvotes: 2

Related Questions