PostgreSQL如何查询数据库中存储的JSON数组内的指定字段值

以下默认存储该JSON数组的表名为user_info，对应的JSON字段名为ext_info，你可以根据实际业务场景替换表名和字段名。

方案1：提取数组第一个元素的name值（同时适配JSON/JSONB字段类型） #

PostgreSQL中JSON操作符->用于返回JSON类型的节点结果，->>用于直接返回节点对应的文本值，JSON数组下标从0开始，查询语句如下：

SELECT ext_info -> 0 ->> 'name' AS name
FROM user_info;

执行后直接返回字符串结果Shahin。

方案2：提取数组中所有元素的name值 #

如果你的JSON数组可能包含多个对象，需要提取所有对象的name值，可以用数组展开函数处理：

• 字段为JSONB类型时：

SELECT jsonb_array_elements(ext_info) ->> 'name' AS name
FROM user_info;

• 字段为JSON类型时：

SELECT json_array_elements(ext_info) ->> 'name' AS name
FROM user_info;

方案3：筛选出name等于Shahin的行 #

如果需要查询数组中包含name为Shahin的记录，可以用以下写法：

• JSONB类型（性能更高，推荐优先用JSONB存储JSON格式数据）：

SELECT *
FROM user_info
WHERE ext_info @> '[{"name": "Shahin"}]'::jsonb;

• JSON类型：

SELECT *
FROM user_info
WHERE EXISTS (
    SELECT 1 
    FROM json_array_elements(ext_info) elem
    WHERE elem ->> 'name' = 'Shahin'
);

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