PostgreSQL 10.20：查询JSON数组中含指定字符串的text键值

原查询将JSON字段转为文本后用LIKE匹配，会误匹配JSON结构中其他键（比如userID）的内容，要精准匹配数组内对象的text键值，可使用以下两种方法：

方法一：通过展开JSON数组筛选 #

SELECT DISTINCT r.workflowid
FROM cyto_records r
JOIN cyto_record_results rr ON r.recordid = rr.recordid
JOIN json_array_elements(rr.interval_note) AS elem
ON elem->>'text' LIKE '%bb%';

• json_array_elements(rr.interval_note)会把JSON数组拆分为单个JSON对象
• elem->>'text'提取每个对象中text字段的文本值，仅对该值做LIKE匹配，避免误匹配其他键

方法二：使用EXISTS子查询（更高效） #

SELECT DISTINCT r.workflowid
FROM cyto_records r
JOIN cyto_record_results rr ON r.recordid = rr.recordid
WHERE EXISTS (
    SELECT 1
    FROM json_array_elements(rr.interval_note) AS elem
    WHERE elem->>'text' LIKE '%bb%'
);

• 子查询会检查当前记录的interval_note数组中是否存在text值包含'bb'的对象，存在则保留主查询记录
• 这种方式无需展开后再去重（虽然主查询仍保留DISTINCT确保结果唯一），性能更优

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