FROM与WHERE含子查询时GROUP BY无结果的问题排查

问题场景 #

原本有一段可正常运行的SQL（用于返回去重的accnt_id和group_id），修改为按accnt_id分组统计group_id数量后，查询无报错但未返回任何数据。

原可运行SQL #

SELECT DISTINCT
  accnt_id,
  group_id
FROM
  (
    SELECT
      data ->> 'accountId' as accnt_id,
      data ->> 'resourceId' as group_id,
      jsonb_array_elements(data -> 'configuration' -> 'ipPerms') ->> 'toPort' as to_port
    FROM
      groups
  ) AS subquery
WHERE
  to_port :: integer IN (
    SELECT
      p.port
    FROM
      databases d
      JOIN ports p ON d.id = p.database_id
  );

修改后的统计SQL（无数据返回） #

SELECT 
  accnt_id,
  count(group_id) as num_of_group_ids
FROM
  (
    SELECT
      data ->> 'accountId' as accnt_id,
      data ->> 'resourceId' as group_id,
      jsonb_array_elements(data -> 'configuration' -> 'ipPerms') ->> 'toPort' as to_port
    FROM
      groups
  ) AS subquery
WHERE
  to_port :: integer IN (
    SELECT
      p.port
    FROM
      databases d
      JOIN ports p ON d.id = p.database_id
  )
GROUP BY accnt_id;

表结构与示例数据 #

groups表包含id（INT）和data（JSONB）两个字段，data字段示例数据：

[ 
  { 
    "accountId":"897687", 
    "resourceId":"YHHH688", 
    "configuration":{ 
      "ipPerms":[ { "toPort":1234, "fromPort":0 } ] 
    } 
  }, 
  { 
    "accountId":"8760880", 
    "resourceId":"FDFG688", 
    "configuration":{ 
      "ipPerms":[ { "toPort":5467, "fromPort":0 }, { "toPort":143, "fromPort":0 } ] 
    } 
  } 
]

问题原因 #

1. 数组展开导致重复行：jsonb_array_elements会将ipPerms数组中的每个元素拆分成单独行，同一个group_id会对应多行（数组有几个元素就有几行）。原查询用DISTINCT去重，确保每个accnt_id+group_id只出现一次；但修改后的查询没有去重，若ports表中没有匹配任何toPort值，整个查询就无数据返回。
2. 统计逻辑不符合需求：需要统计的是每个accnt_id下唯一group_id的数量，原修改查询用count(group_id)会把同一个group_id的多行重复计数，即使有数据也会得到错误结果。

解决方案 #

方案一：先过滤去重再统计（更高效） #

通过EXISTS判断该组下是否存在匹配的端口，先获取去重的accnt_id和group_id，再统计数量：

SELECT 
  accnt_id,
  count(group_id) as num_of_group_ids
FROM
  (
    SELECT DISTINCT
      data ->> 'accountId' as accnt_id,
      data ->> 'resourceId' as group_id
    FROM
      groups
    WHERE
      EXISTS (
        SELECT 1
        FROM jsonb_array_elements(data -> 'configuration' -> 'ipPerms') AS ip_perm
        WHERE (ip_perm ->> 'toPort')::integer IN (
          SELECT p.port
          FROM databases d
          JOIN ports p ON d.id = p.database_id
        )
      )
  ) AS subquery
GROUP BY accnt_id;

方案二：统计时去重 #

在统计时使用count(DISTINCT group_id)，确保每个group_id只被计数一次：

SELECT 
  accnt_id,
  count(DISTINCT group_id) as num_of_group_ids
FROM
  (
    SELECT
      data ->> 'accountId' as accnt_id,
      data ->> 'resourceId' as group_id,
      jsonb_array_elements(data -> 'configuration' -> 'ipPerms') ->> 'toPort' as to_port
    FROM
      groups
  ) AS subquery
WHERE
  to_port :: integer IN (
    SELECT p.port
    FROM databases d
    JOIN ports p ON d.id = p.database_id
  )
GROUP BY accnt_id;

说明 #

• 方案一避免了不必要的行展开，性能更优，逻辑更贴合原查询的意图（只要组内有一个端口匹配就统计该组）。
• 方案二保留了原查询的子查询结构，通过统计时去重得到正确结果。

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