PostgreSQL：将多连接结果合并为单行并生成无重复JSON数组

The issue you're hitting is that joining main, sub1, and sub2 directly creates a Cartesian product between the rows of sub1 and sub2 for the same main_id. Since sub1 has 2 rows and sub2 has 1 row for main_id=1, you end up with 2 intermediate rows—so array_agg(sub2.lang) picks up the 1 twice, resulting in [1,1].

Here are two reliable ways to get your desired output:

1. Aggregate Subtables First (Recommended) #

This approach avoids the Cartesian product entirely by pre-aggregating each subtable before joining to the main table. It's more efficient, especially as your tables grow:

SELECT 
    main.id,
    sub1.langs AS sub1,
    sub2.langs AS sub2
FROM main
LEFT JOIN (
    SELECT main_id, array_to_json(array_agg(lang)) AS langs
    FROM sub1
    GROUP BY main_id
) sub1 ON main.id = sub1.main_id
LEFT JOIN (
    SELECT main_id, array_to_json(array_agg(lang)) AS langs
    FROM sub2
    GROUP BY main_id
) sub2 ON main.id = sub2.main_id
WHERE main.id = 1;

This query will return exactly what you're expecting:

2. Use DISTINCT in Aggregation (Quick Fix) #

If you prefer to keep your original JOIN structure, you can add DISTINCT inside the array_agg for sub2.lang to eliminate duplicates. Note that this will also remove any intentional duplicate values in sub2, so only use this if you don't need to preserve those:

SELECT 
    main.id,
    array_to_json(array_agg(sub1.lang)) AS sub1,
    array_to_json(array_agg(DISTINCT sub2.lang)) AS sub2
FROM main 
INNER JOIN sub1 ON main.id = sub1.main_id 
INNER JOIN sub2 ON main.id = sub2.main_id 
WHERE main.id = 1 
GROUP BY main.id;

Both methods work, but the first one is better for performance and data integrity if you might have legitimate duplicates in your subtables that you want to keep.

内容的提问来源于stack exchange，提问作者alexс