如何在Postgres中从文本存储的JSON数组内提取ID值

你可以通过以下几步完成提取：

• 第一步：将存储为Text类型的JSON数组强转为jsonb（推荐，性能和功能都优于json类型）
• 第二步：用jsonb_array_elements展开外层的二维JSON数组
• 第三步：过滤出子数组第二个元素（JSON数组索引从0开始，对应索引为1）为id的条目，取第三个元素（对应索引为2）的值即可

基础查询语句 #

假设你的表名为your_table，存储该文本的字段名为json_arr_text，基础查询写法如下：

SELECT (arr_element ->> 2)::int AS id_value
FROM your_table,
     jsonb_array_elements(json_arr_text::jsonb) AS arr_element
WHERE arr_element ->> 1 = 'id';

保留全量行的优化写法 #

如果需要保留原表所有行，即便某行数据中没有id键也不丢弃，返回null即可，可以用左关联LATERAL的写法：

SELECT t.*, (a.arr_element ->> 2)::int AS id_value
FROM your_table t
LEFT JOIN LATERAL jsonb_array_elements(t.json_arr_text::jsonb) AS a
  ON a.arr_element ->> 1 = 'id';

异常兼容写法 #

如果部分行的文本内容不合法，无法转成JSON导致查询报错，可以增加合法性校验过滤：

SELECT (arr_element ->> 2)::int AS id_value
FROM your_table
WHERE pg_input_is_valid(json_arr_text, 'jsonb')
, jsonb_array_elements(json_arr_text::jsonb) AS arr_element
WHERE arr_element ->> 1 = 'id';

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