Postgres 14

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

CREATE TABLE my_table (
  id integer NOT NULL,
  foreign_key_column_id integer NOT NULL,
  column1_value_1 character varying NOT NULL,
  column1_value_2 character varying,
  column1_value_3 character varying
);

INSERT INTO my_table VALUES
  (1, 12, 'Foo', NULL, NULL)              -- NULL values to be removed
, (2, 12, 'Bar %{1}', NULL, 'Bar %{2}')
, (3, 1234, 'Baz %{1}', 'Baz %{2}', NULL)
, (4, 1234, 'Blub', 'Baz %{2}', NULL)     -- dupe to be removed
4 rows affected

SELECT foreign_key_column_id, jsonb_agg(DISTINCT c.col1)
FROM   my_table t
LEFT   JOIN LATERAL (
   VALUES
     (column1_value_1)
   , (column1_value_2)
   , (column1_value_3)
   ) c(col1) ON col1 IS NOT NULL
GROUP  BY 1;


foreign_key_column_id
jsonb_agg


12
["Bar %{1}", "Bar %{2}", "Foo"]

1234
["Baz %{1}", "Baz %{2}", "Blub"]


-- full table
CREATE TABLE my_table3 (
  id integer NOT NULL
, foreign_key_column_id integer NOT NULL
, column1_value_1 character varying NOT NULL
, column1_value_2 character varying
, column1_value_3 character varying
, column2_value_1 character varying NOT NULL
, column2_value_2 character varying
, column2_value_3 character varying
, column3_value_1 character varying NOT NULL
, column3_value_2 character varying
, column3_value_3 character varying
);

INSERT INTO my_table3 VALUES
  (1, 12, 'Foo', NULL, NULL, 'Foo2', NULL, NULL, 'Foo3', NULL, NULL)
, (2, 12, 'Bar %{1}', NULL, 'Bar %{2}', 'Bar2 %{1}', NULL, 'Bar2 %{2}', 'Bar3 %{1}', NULL, 'Bar3 %{2}')
, (3, 1234, 'Baz %{1}', 'Baz %{2}', NULL, 'Baz2 %{1}', 'Baz2 %{2}', NULL, 'Baz3 %{1}', 'Baz3 %{2}', NULL)
, (4, 1234, 'Blub', 'Baz %{2}', NULL, 'Blub2', NULL, 'Baz2 %{2}', 'Blub3', 'Baz3 %{2}', NULL)
4 rows affected

-- process all at once
SELECT foreign_key_column_id
     , jsonb_agg(DISTINCT c.col1) AS column1
     , jsonb_agg(DISTINCT c.col2) FILTER (WHERE c.col2 IS NOT NULL) AS column2 -- !
     , jsonb_agg(DISTINCT c.col3) FILTER (WHERE c.col3 IS NOT NULL) AS column3
FROM   my_table3 t
LEFT   JOIN LATERAL (
   VALUES
     (column1_value_1, column2_value_1, column3_value_1)
   , (column1_value_2, column2_value_2, column3_value_2)
   , (column1_value_3, column2_value_3, column3_value_3)
   ) c(col1, col2, col3) ON NOT (col1, col2, col3) IS NULL  -- !
GROUP  BY 1;


foreign_key_column_id
column1
column2
column3


12
["Bar %{1}", "Bar %{2}", "Foo"]
["Bar2 %{1}", "Bar2 %{2}", "Foo2"]
["Bar3 %{1}", "Bar3 %{2}", "Foo3"]

1234
["Baz %{1}", "Baz %{2}", "Blub", null]
["Baz2 %{1}", "Baz2 %{2}", "Blub2"]
["Baz3 %{1}", "Baz3 %{2}", "Blub3"]

foreign_key_column_id	jsonb_agg
12	["Bar %{1}", "Bar %{2}", "Foo"]
1234	["Baz %{1}", "Baz %{2}", "Blub"]

foreign_key_column_id	column1	column2	column3
12	["Bar %{1}", "Bar %{2}", "Foo"]	["Bar2 %{1}", "Bar2 %{2}", "Foo2"]	["Bar3 %{1}", "Bar3 %{2}", "Foo3"]
1234	["Baz %{1}", "Baz %{2}", "Blub", null]	["Baz2 %{1}", "Baz2 %{2}", "Blub2"]	["Baz3 %{1}", "Baz3 %{2}", "Blub3"]