PostgreSQL如何提取jsonb列数组内多JSON的指定字段值

针对jsonb类型字段存储规则数组、需要批量提取rules下所有name值的需求，不需要拆分现有表结构，两种要求的返回形式都可以直接通过内置函数实现：

• 多行返回单个name值（灵活度最高，支持后续过滤、关联查询等操作）
  用jsonb_array_elements函数把JSON数组拆为多行数据即可，示例SQL：

  SELECT rule_item ->> 'name' AS rule_name
  FROM <table_name>,
       jsonb_array_elements(attr -> 'rules') AS rule_item;
  

  执行后每一行对应一个规则的name值，你给出的测试数据会返回两行结果，值分别为test_01、test_02。

• 单行返回逗号拼接的所有name值
  拆分数组后用string_agg聚合拼接即可，示例SQL：

  SELECT string_agg(rule_item ->> 'name', ',') AS concatenated_rule_names
  FROM <table_name>,
       jsonb_array_elements(attr -> 'rules') AS rule_item;
  

  执行后直接返回test_01,test_02格式的单值结果。

注：如果存在部分行的rules字段为空、不是合法数组的情况，可以给取值逻辑套一层coalesce(attr -> 'rules', '[]'::jsonb)兜底，避免查询报错。

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