如何找出两个同结构嵌套SQL表中元素数量不同的ID

假设两张表分别为table_a和table_b，核心思路是分别计算两张表中每个ID对应的elements元素数量，再通过关联对比找出数量不相等的ID。

不同SQL方言的实现代码 #

PostgreSQL（处理JSON/JSONB类型的嵌套列）

SELECT COALESCE(a.id, b.id) AS id
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id
WHERE jsonb_array_length(a.elements) != jsonb_array_length(b.elements)
   OR (a.elements IS NULL AND b.elements IS NOT NULL)
   OR (a.elements IS NOT NULL AND b.elements IS NULL);

MySQL（处理JSON类型的嵌套列）

SELECT COALESCE(a.id, b.id) AS id
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id
WHERE JSON_LENGTH(a.elements) != JSON_LENGTH(b.elements)
   OR (a.elements IS NULL AND b.elements IS NOT NULL)
   OR (a.elements IS NOT NULL AND b.elements IS NULL);

注：MySQL 8.0+支持FULL OUTER JOIN，若版本较低，可改用LEFT JOIN+RIGHT JOIN的联合查询替代

SQL Server（处理JSON类型的嵌套列）

SELECT COALESCE(a.id, b.id) AS id
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id
WHERE (SELECT COUNT(*) FROM OPENJSON(a.elements)) != (SELECT COUNT(*) FROM OPENJSON(b.elements))
   OR (a.elements IS NULL AND b.elements IS NOT NULL)
   OR (a.elements IS NOT NULL AND b.elements IS NULL);

逻辑说明 #

• 使用FULL OUTER JOIN确保覆盖仅存在于其中一张表的ID（这类ID的元素数量默认视为不一致）
• 通过对应数据库的JSON数组长度计算函数，获取elements的元素数量
• 对比两侧数量，筛选出不相等的ID，同时处理其中一侧elements为NULL的情况

示例验证 #

针对案例2：table3中id=1的elements有3个元素，table4中id=1的elements有2个元素，将上述代码中的表名替换为table3和table4，执行后将返回：

id
----
1

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