PostgreSQL如何从JSONB提取数组用于SELECT...IN子句？

错误原因分析 #

你当前的SQL存在两个问题：

1. json_array_elements是处理原生JSON类型的函数，但jsonb_extract_path_text返回的是字符串格式的数组（比如"[1,2,3]"），并非JSON数组对象，因此无法被解析。
2. 嵌套子查询缺少括号包裹，语法不合法。

正确解决方案 #

方案1：拆分JSONB数组为整数行

直接使用JSONB专用的jsonb_array_elements函数，配合路径操作符定位数组，再转换为整数类型：

SELECT (jsonb_array_elements('{"data": {"types": [1,2,3]}}'::jsonb -> 'data' -> 'types'))::int AS type_id;

执行后得到单列结果：

如果数据来自表字段（比如表test有jsonb_col字段），直接替换为字段即可：

SELECT (jsonb_array_elements(jsonb_col -> 'data' -> 'types'))::int AS type_id
FROM test;

方案2：直接生成整数数组用于IN/ANY子句

如果不需要拆分行，而是要直接得到可用于IN的数组，用jsonb_array_to_text_array转成文本数组后强转为整数数组，配合ANY操作符使用（效果等同于IN）：

-- 获取整数数组
SELECT (jsonb_array_to_text_array('{"data": {"types": [1,2,3]}}'::jsonb -> 'data' -> 'types'))::int[] AS type_array;

-- 用于SELECT...ANY的示例
SELECT * FROM your_target_table
WHERE your_int_column = ANY(
  (jsonb_array_to_text_array('{"data": {"types": [1,2,3]}}'::jsonb -> 'data' -> 'types'))::int[]
);

修正你原有的SQL（不推荐，效率较低）

如果一定要沿用你的思路，需要把字符串转成JSON类型并修正子查询语法：

SELECT (json_array_elements(
  (SELECT jsonb_extract_path_text('{"data": {"types": [1,2,3]}}'::jsonb, 'data', 'types'))::json
))::int;

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