Azure SQL中JSON数据类型无法在OPENJSON函数中使用的问题求助

问题描述 #

过去一个月基于Azure SQL原生JSON数据类型开发解决方案，此前运行一切正常，但近期原有函数突然报错：

Msg 13658, Level 16, State 2, Line 10
JSON data type cannot be used in OpenJson function.

经排查，错误源于使用CROSS APPLY从JSON列提取数据的查询，将列转换为NVARCHAR(MAX)后错误消失。但微软官方文档明确表示所有JSON函数均支持JSON类型，无需修改代码。

当前环境：

• Azure DB版本：12.0.2000.8
• 数据库兼容性级别：170

以下是简化后的测试表及查询代码：

DROP TABLE IF EXISTS [dbo].[exampleTable];

CREATE TABLE [dbo].[exampleTable](
    [functionName]  AS (CONVERT([nvarchar](50),(json_value([mappingFunction],'$.name')) collate SQL_Latin1_General_CP1_CI_AS)) PERSISTED,
    [mappingFunction] [json] NOT NULL
);
GO

INSERT INTO [dbo].[exampleTable] ([mappingFunction])
VALUES ('{
    "name": "lookupAttribute",
    "function": "ValueLookup",
    "type": "table",
    "description": "This function retrieves the attribute value from the specified entity",
    "parameters": [
        {
            "name": "lookupEntity",
            "type": "sysname",
            "description": "This is the name of the entity that contains the required attribute",
            "optional": "false"
        },
        {
            "name": "lookupAttribute",
            "type": "sysname",
            "description": "The attribute whose value will be returned",
            "optional": "false"
        },
        {
            "name": "lookupEnvironmentType",
            "type": "sysname",
            "description": "Specifies which environment id is returned",
            "optional": "true",
            "valid": [
                "SOURCE",
                "DESTINATION"
            ],
            "default": "SOURCE"
        },
        {
            "name": "filterBy",
            "type": "json",
            "description": "This parameter can be used to filter the recordset by specifying the attribute name & value pairs",
            "optional": "true"
        }
    ],
    "returns": [
        {
            "name": "environmentId",
            "type": "uniqueidentifier",
            "description": "This is either the source environment, or valid destination environments (depending on the lookupEnvironmentType"
        },
        {
            "name": "rowSK",
            "type": "uniqueidentifier",
            "description": "This is either the source environment, or valid destination environments (depending on the lookupEnvironmentType"
        },
        {
            "name": "keyValue",
            "type": "nvarchar(max)",
            "description": "This is the value for the key attribute for each row"
        },
        {
            "name": "content",
            "type": "nvarchar(max)",
            "description": "This is the value of the attribute specified in the lookupAttribute parameter"
        }
    ]
}');
GO


SELECT p.[name]
  FROM [dbo].[exampleTable] f
  CROSS APPLY OPENJSON([mappingFunction], '$.parameters') 
    WITH ([name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS) p;

请问是否有其他人遇到此问题？

解决方案与相关情况 #

这个问题并非个例，已有不少用户在Azure SQL DB的特定旧版本中遇到过，本质是JSON类型与OPENJSON函数的兼容性bug，以下是可行的处理方式：

• 临时规避方案：继续使用你发现的转换方式，将JSON列显式转为NVARCHAR(MAX)后传入OPENJSON，修改后的查询示例：

  SELECT p.[name]
    FROM [dbo].[exampleTable] f
    CROSS APPLY OPENJSON(CONVERT(NVARCHAR(MAX), [mappingFunction]), '$.parameters') 
      WITH ([name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS) p;
  

• 升级数据库版本：你当前使用的12.0.2000.8属于较旧的Azure SQL DB版本，微软后续的补丁更新已经修复了这个问题，建议将数据库升级到最新的稳定版本，彻底解决该兼容性问题。
• 重置兼容性级别：如果暂时无法升级，可以尝试重置数据库兼容性级别，清除可能存在的元数据缓存问题：

  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 160;
  GO
  ALTER DATABASE YourDatabaseName SET COMPATIBILITY_LEVEL = 170;
  GO
  

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