PostgreSQL如何查询jsonb列中JSON集合的第N个位置元素

你插入数据时groups对应的值是普通字符串，并非PostgreSQL可识别的原生JSON数组格式，同时字符串内的数组元素没有加双引号，不符合JSON语法规范，因此直接使用JSON路径查询语法会报错。

分两种场景处理： #

场景1：可以调整存储格式（推荐） #

修改插入语句，将groups存为原生JSON数组：

-- 清空原有测试数据后重新插入符合JSON规范的内容
truncate table myjson;
insert into myjson(jsondetails) values ('{"groups": ["group1", "group2"]}');

此时直接用你之前尝试的路径查询语法即可获取对应元素（JSON数组下标从0开始，取第2个元素下标为1）：

select jsondetails#>> '{groups,1}' from myjson;

场景2：无法修改现有存储结构 #

子场景2.1 存储的字符串数组元素带双引号（即存储值为"[\"group1\",\"group2\"]"） #

先取出字符串转成JSONB类型再取下标：

select ( (jsondetails->>'groups')::jsonb ) ->> 1 as target_group from myjson;

子场景2.2 存储的字符串数组元素无引号（和你当前插入的测试数据格式一致） #

用字符串正则替换+切割的方式提取元素（PostgreSQL普通数组下标从1开始，取第2个元素下标为2）：

select 
  (string_to_array( regexp_replace(jsondetails->>'groups', '\[|\]', '', 'g'), ',' ))[2] 
as target_group from myjson;

如果需要提取任意第N个位置的元素：

• 原生JSON数组场景：把路径/下标中的数值替换为N-1即可
• 字符串切割场景：把数组下标替换为N即可

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