问题:将一组 JSON 对象合并到 Postgres 中的一个 JSON 列

我有两个表products和products_ext可以基本上简化为这种基本形式:

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
  );

让我们假设模拟数据

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"');

我可以使用查询

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";

产生

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

但我不能让它产生一个合并的 JSON。 Postgres 10 中是否有一种简单的方法可以将多个 JSON 的数组合并为一个会产生的 JSON?

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

主键对“product_id”和“key”已经确保没有键冲突。products中可能有一些行在products_ext中没有任何数据,在这种情况下,应该提供一个空的 JSON 对象。

解答

demo:db<>fiddle

使用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;

编辑空product_ext值:

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;