PostgreSQL 9.5提取JSONB数组内哈希对象的id值

没问题，我来帮你搞定这个需求！你之前的查询只是把records整个数组取出来，没有拆解数组里的单个元素，所以需要用PostgreSQL的JSON数组展开函数来处理。

基础方法：提取每个id为单独行 #

PostgreSQL 9.5提供了jsonb_array_elements函数，它能把JSONB数组拆分成单独的行（每行对应数组里的一个对象），之后我们就可以从每个对象里提取id值了。

试试这个SQL：

SELECT elem->>'id' AS record_id
FROM my_table,
     jsonb_array_elements(my_json->'records') AS elem
WHERE my_json->>'records' IS NOT NULL 
  AND my_json #> '{records}' != '[]'

解释：

• jsonb_array_elements(my_json->'records') AS elem：把my_json里的records数组拆分成多行，每一行的elem就是数组里的一个{"id":x, "name":"xxx"}对象
• elem->>'id'：从每个对象里提取id字段，并转为文本类型（如果需要整数类型，可以用(elem->>'id')::int做类型转换）
• 你的原WHERE条件保留了，确保只处理有非空records数组的行

进阶：把id聚合为数组或字符串 #

如果你想把同一行的所有id合并成一个数组或者逗号分隔的字符串，可以用聚合函数：

聚合成文本数组

SELECT 
  my_table.id AS table_id, -- 假设你的表有主键id，用来标识原表的行
  array_agg(elem->>'id' ORDER BY elem->>'id') AS record_ids
FROM my_table,
     jsonb_array_elements(my_json->'records') AS elem
WHERE my_json->>'records' IS NOT NULL 
  AND my_json #> '{records}' != '[]'
GROUP BY my_table.id

聚合成逗号分隔的字符串

SELECT 
  my_table.id AS table_id,
  string_agg(elem->>'id', ', ' ORDER BY elem->>'id') AS record_ids_string
FROM my_table,
     jsonb_array_elements(my_json->'records') AS elem
WHERE my_json->>'records' IS NOT NULL 
  AND my_json #> '{records}' != '[]'
GROUP BY my_table.id

这样就能得到你需要的所有records下的id值啦！

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