We've upgraded one of our instances from 14.10 to 16.1 at AWS All fine, except for a certain output which became different. At another instance running at 14.7 we are able to reproduce this correct answer. Removing the `DISTINCT` or replacing `(SELECT a.id)` with `a.id` does solve it. It looks like a reference issue.
DROP TABLE IF EXISTS pg_temp.alldata; create temp table pg_temp.alldata AS ( SELECT 'val_A' AS id );
SELECT a.id as table_value, STRING_AGG( 'val_B', ', ') AS string_agg_value, JSON_AGG ( JSON_BUILD_OBJECT ( 'val_1', a.id, 'val_2', (SELECT a.id) ) ) AS zzz_2 FROM pg_temp.alldata a GROUP BY id;
at: PostgreSQL 14.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit output: table_value|string_agg_value|zzz_2 | -----------+----------------+----------------------------------------+ val_A |val_B |[{"val_1" : "val_A", "val_2" : "val_A"}]|
at: PostgreSQL 16.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit output: table_value|string_agg_value|zzz_2 | -----------+----------------+----------------------------------------+ val_A |val_B |[{"val_1" : "val_A", "val_2" : "val_B"}]| -- why is val_B suddenly here?