如何在PostgreSQL中查询JSON列：获取指定UserId的数据

针对JSON类型列的查询语句 #

如果你的value列是JSON类型，用下面的SQL展开数组并筛选：

SELECT du.*
FROM participant_preference pp,
     json_array_elements(pp.value->'DiaryUsers') AS du
WHERE du->>'UserId' = 'HUV65PV1';

针对JSONB类型列的查询语句（推荐） #

如果value列是JSONB类型（建议将JSON列转为JSONB，查询性能更好），可以用：

SELECT du.*
FROM participant_preference pp,
     jsonb_array_elements(pp.value->'DiaryUsers') AS du
WHERE du->>'UserId' = 'HUV65PV1';

关键语法说明 #

• pp.value->'DiaryUsers'：从value列中提取DiaryUsers数组
• json_array_elements/jsonb_array_elements：把数组拆分成独立的行记录，方便逐行筛选
• du->>'UserId'：将数组元素里的UserId字段转为文本格式，用于和目标值HUV65PV1匹配

如果需要返回表的整行数据 #

如果你不想只拿数组里的匹配元素，而是要返回participant_preference表中包含该用户的整行记录，可以用：

SELECT pp.*
FROM participant_preference pp
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(pp.value->'DiaryUsers') AS du
    WHERE du->>'UserId' = 'HUV65PV1'
);

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