如何在PostgreSQL中使用JSON数组作为IN操作符的取值来源

你的查询没返回结果，核心原因是json_array_elements解析JSON数组后得到的是JSON类型值，转成VARCHAR时会保留原始的双引号，而orders表的customer字段是不带引号的纯字符串，导致两者无法匹配。

举个例子：如果users表中authorized_customers的值是["alice", "bob"]，CAST(json_array_elements(authorized_customers) AS VARCHAR)返回的是"alice"和"bob"（带双引号），但orders表的customer字段存的是alice和bob，自然匹配不上。

两种修正方案 #

方案1：用json_array_elements_text直接提取文本

这个函数会直接返回不带引号的字符串，无需额外转换：

SELECT * 
FROM orders 
WHERE customer IN (
    SELECT json_array_elements_text(authorized_customers) 
    FROM users 
    WHERE id = 1
);

方案2：使用JSON路径操作符->>提取值

通过生成数组索引遍历JSON数组，用->>提取对应位置的字符串值：

SELECT * 
FROM orders 
WHERE customer IN (
    SELECT authorized_customers ->> idx 
    FROM users, generate_series(0, json_array_length(authorized_customers)-1) AS idx
    WHERE id = 1
);

快速排查方法 #

先单独执行子查询，查看返回的内容格式：

SELECT json_array_elements_text(authorized_customers) FROM users WHERE id=1;

把结果和orders表的customer字段值对比，就能直观看到格式差异。

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