Postgres 10

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

SELECT * FROM products;

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



product_id


test1

test2

test3


3 rows affected

SELECT * FROM products_ext;


product_id
key
value


test1
foo
"Foo"

test1
bar
"Bar"

test2
foo
"Foo"


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;


product_id
coalesce


test1
{ "foo" : "Foo", "bar" : "Bar" }

test2
{ "foo" : "Foo" }

test3
{}

product_id	coalesce
test1	{ "foo" : "Foo", "bar" : "Bar" }
test2	{ "foo" : "Foo" }
test3	{}