JSON data type cannot be used in OpenJson function

I've been working on a solution in Azure SQL using the native JSON data type for the last month. Everything has been going well, but I've just run one of the functions that has been working flawlessly and now I'm getting the following error:

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

This was an odd error when I first saw it, earlier today. I tracked it down to a query that was using a CROSS APPLY to extract JSON data from a JSON column.

Converting the column's type to NVARCHAR(MAX) in the OPENJSON call removed the error.

It appears that, for no apparent reason, you can now no longer pass the native JSON data type into this function.

These means I have to either go back through a whole load of functions and convert the everything on the fly to NVARCHAR(MAX), or change the data types of the table columns from JSON to NVARCHAR(MAX). Which seems ludicrous.

Looking at the Microsoft JSON documentation it says:

All JSON functions support the json type with no code changes or usage difference necessary.

And there's a link to the JSON functions, of which OPENJSON is included.

Here's a cut-down version of the table, with an example of the JSON that was working:

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;

Is anyone else seeing this? Nothing is showing up when I query that error code/message.

My Azure DB version is 12.0.2000.8, compatibility level 170.

The json data type is still in preview as of writing, and the documentation specifically states that OPENJSON does not yet work with the json datatype (see the last bullet point):

Limitations

• The behavior of CAST ( ... AS JSON) returns a json type, but the sp_describe_first_result_set system stored procedure doesn't correctly return the json data type. Therefore, many data access clients and driver will see a varchar or nvarchar data type.

  • Currently, TDS >= 7.4 (with UTF-8) sees varchar(max) with Latin_General_100_bin2_utf8.
  • Currently, TDS < 7.4 sees nvarchar(max) with database collation.

• Currently, the OPENJSON() function doesn't accept the json type, currently that is an implicit conversion. Explicitly convert to nvarchar(max) first.

Based on this, it is most certainly planned to support it, but while in preview, at least, it appear to not be. The solution, as the documentation states, is to explicitly CAST/CONVERT the value to an nvarchar(MAX) first.

Interesting, however, this problem doesn't appear in SQL Server 2025 (CTP2.0) - 17.0.700.9. The limitation has always been in the documentation, however, this could be a regression in Azure SQL Database. Either way, the documentation is wrong.