SQL多表关联后JSON_ARRAYAGG生成的参数数组出现重复项问题求助

Hey there! Let’s break down what’s going on with your query and fix that duplicate parameter issue you’re seeing.

Why the Duplicates Are Happening #

You’re spot-on with your hunch—this is caused by a cartesian product from joining multiple unrelated tables. When you join rooms to both roomdevices (which links to devices) and roomdata in the same query, there’s no direct relationship between roomdevices and roomdata.

For example: If a room has 2 devices and 1 parameter, the join will create 2 separate rows (one for each device paired with the single parameter). When json_arrayagg runs, it picks up that parameter twice—once for each device row.

Solution 1: Pre-Aggregate Room Data (Most Reliable) #

The cleanest fix is to aggregate your roomdata into the JSON array before joining it to the rest of the tables. This way, you avoid the cartesian product entirely.

Here’s the adjusted query:

SELECT 
    r.identifier, 
    r.room_name, 
    COALESCE(rdata.parameters, JSON_ARRAY()) AS parameters,
    COALESCE(json_arrayagg(JSON_OBJECT(
        "identifier", d.identifier, 
        "friendly_name", d.friendly_name, 
        "type", d.type, 
        "gateway", d.gateway
    )), JSON_ARRAY()) AS devices 
FROM rooms AS r 
LEFT JOIN roomdevices AS rd ON rd.room_identifier = r.identifier 
LEFT JOIN devices AS d ON d.identifier = rd.device_identifier 
LEFT JOIN (
    -- Subquery to pre-aggregate room parameters into a JSON array
    SELECT 
        room_identifier,
        json_arrayagg(JSON_OBJECT("key", parameter_key, "value", value)) AS parameters
    FROM roomdata
    GROUP BY room_identifier
) AS rdata ON rdata.room_identifier = r.identifier 
GROUP BY r.identifier, r.room_name, rdata.parameters

I added COALESCE here to return an empty JSON array (JSON_ARRAY()) for rooms with no parameters or devices, instead of NULL—this makes the output more consistent.

Solution 2: Use DISTINCT in json_arrayagg (Quick Fix, Caveats) #

If you want a simpler tweak, you can add DISTINCT inside the json_arrayagg for parameters. This will deduplicate the JSON objects in the array.

Note: Only use this if you’re sure you’ll never have legitimate duplicate parameters (same key and value) for a single room—this method will remove those too.

Example:

SELECT DISTINCT 
    r.identifier, 
    r.room_name, 
    COALESCE(json_arrayagg(DISTINCT JSON_OBJECT("key", rdata.parameter_key, "value", rdata.value)), JSON_ARRAY()) AS parameters, 
    COALESCE(json_arrayagg(JSON_OBJECT(
        "identifier", d.identifier, 
        "friendly_name", d.friendly_name, 
        "type", d.type, 
        "gateway", d.gateway
    )), JSON_ARRAY()) AS devices 
FROM rooms AS r 
LEFT JOIN roomdevices AS rd ON rd.room_identifier = r.identifier 
LEFT JOIN devices AS d ON d.identifier = rd.device_identifier 
LEFT JOIN roomdata as rdata ON rdata.room_identifier = r.identifier 
GROUP BY r.identifier

Why Your First Query Worked #

In your initial query, you only joined rooms → roomdevices → devices—this is a chain of one-to-many relationships, so no cartesian product was created. Each room’s devices are linked directly through roomdevices, so the aggregation worked as expected.

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