Rails+Postgres下JSONB列嵌套数组数据筛选与SQL简化问题

简化后可直接使用的SQL #

SELECT
  d.id,
  COALESCE(
    ARRAY_AGG(
      CONCAT_WS(' ', elem->>'first_name', elem->>'last_name')
      ORDER BY arr.ord
    ),
    '{}'::text[]
  ) AS _board
FROM my_table d
LEFT JOIN LATERAL JSONB_ARRAY_ELEMENTS(
  -- 统一处理JSON null、SQL null场景，转为空数组
  COALESCE(NULLIF(d.data->'board_members', 'null'::jsonb), '[]'::jsonb)
) WITH ORDINALITY arr(elem, ord) ON TRUE
-- 同时匹配布尔true和字符串'true'的筛选条件
WHERE arr.elem->>'ind' = 'true'
GROUP BY d.id;

优化说明&逻辑解释 #

1. 去掉了原SQL中重复的数组展开、字段提取逻辑，通过LATERAL JOIN将board_members数组仅展开一次，性能和可读性都大幅提升
2. 用->/->>运算符替代冗长的jsonb_extract_path函数调用，写法更简洁
3. 用NULLIF + COALESCE组合替代原SQL的CASE判断，统一处理board_members为JSON null、SQL null的场景，同时避免了原SQL中null场景下生成无意义空格字符串的问题
4. 筛选逻辑直接通过elem->>'ind' = 'true'实现：->>运算符会将JSON值统一转为文本输出，布尔类型的true和字符串类型的'true'最终输出的文本都是true，刚好匹配你的筛选需求
5. 用CONCAT_WS拼接姓名，避免单个字段为null时整个拼接结果变为null的问题
6. 新增WITH ORDINALITY保留原数组的元素顺序，聚合后的姓名顺序和原数组中符合条件的元素顺序一致，不需要可以删除对应代码
7. 外层COALESCE包裹ARRAY_AGG，保证没有符合条件的元素时返回空数组，而不是null值
8. 去掉了不必要的嵌套子查询和重复GROUP BY逻辑，结构更清晰

内容的提问来源于stack exchange，提问作者B-M