Azure SQL中如何将JSON数组字段值用于IN语句查询

问题描述 #

在Azure SQL Server中执行以下查询，试图通过JSON数组格式的外键值查询foo_table的数据：

SELECT * FROM foo_table WHERE foo_table_id IN (SELECT json_ids_field FROM bar_table WHERE bar_table_id_field= 1);

查询执行失败，报错信息如下：

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value '[29,56,57,71,138,145,195]' to data type int

核心问题是json_ids_field存储的是JSON格式的数组字符串，SQL无法直接将其识别为可用于IN子句的数值列表，需要先反序列化该字段。

解决方案 #

利用Azure SQL Server内置的OPENJSON函数解析JSON数组，将其转换为行集后再用于条件匹配，有两种常用写法：

写法一：IN子句结合OPENJSON #

SELECT * 
FROM foo_table 
WHERE foo_table_id IN (
    -- 解析JSON数组并转换为int类型
    SELECT CAST(value AS INT)
    FROM bar_table
    -- 关联解析后的JSON行集
    CROSS APPLY OPENJSON(json_ids_field)
    WHERE bar_table_id_field = 1
);

写法二：JOIN关联解析后的行集 #

如果数据量较大，JOIN写法的性能可能更优：

SELECT ft.*
FROM foo_table ft
INNER JOIN (
    SELECT CAST(value AS INT) AS match_id
    FROM bar_table
    CROSS APPLY OPENJSON(json_ids_field)
    WHERE bar_table_id_field = 1
) parsed_ids ON ft.foo_table_id = parsed_ids.match_id;

关键说明 #

• OPENJSON(json_ids_field)会将JSON数组字符串拆分为多行结果集，每行的value字段对应数组中的一个元素（默认类型为nvarchar）。
• 通过CAST(value AS INT)将字符串元素转换为与foo_table_id匹配的int类型，避免类型转换错误。
• CROSS APPLY用于将bar_table中的每行数据与解析后的JSON行集进行关联，确保只处理目标行的JSON数组。

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