JSON data type cannot be used in OpenJson function

Question

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.

Can you clarify how the JSON column is defined in your table schema, and share the minimal SQL snippet that triggers the error? Also, did this same query work earlier without type conversion, or has something in your environment (like a recent update) changed? This will help identify if it's a regression or expected behavior. — Salt
– Salt, Commented May 20 at 13:43
I've added a cut-down version of the table, plus some of the json that was working previously — Tom Hodson
– Tom Hodson, Commented May 20 at 14:18
"All JSON functions support the json type with no code changes or usage difference necessary." This does appear to be in conflict with itself. I'll raise this as a PR. — Thom A
– Thom A ♦, Commented May 20 at 14:35
I wouldn't use preview stuff just yet, there's nothing in json datatype which makes it worthwhile until at least SP2. My guess is that they found some security issue with json datatype and had to disable the stuff for now. — siggemannen
– siggemannen, Commented Jul 3 at 22:02

Thom A · Accepted Answer · 2025-05-21 14:54:21Z

1

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.

edited May 21 at 14:54

answered May 20 at 13:59

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Tom Hodson May 20 at 14:07

Thanks for your answer, and absolutely I can see that OPENJSON does not accept the json data type. My problem is that it absolutely was working with the json data type for the last month or so, while I've been developing my code. I was putting the finishing touches to what I've been working on when this error suddenly popped up

Thom A May 20 at 14:34

@TomHodson according Git History, the limitation of it not working with OPENJSON appears to have always been there; it was in the original revision. Perhaps you are mistaken?

Tom Hodson May 20 at 14:34

A post on Brent Ozar's website suggests this is a new error message: brentozar.com/archive/2025/03/…

Tom Hodson May 20 at 14:35

Mistaken how, exactly? My code was working, now it is not.

Thom A May 20 at 14:40

@TomHodson As I stated, that bullet point has been there since the original revision; see the linked version from the original commit for the document.

|

Swa11Sha · Accepted Answer · 2025-05-21 22:07:05Z

I too faced the same issue just recently in Azure SQL DB. I referred to this Microsoft article last month and used the JSON Datatype with OPENJSON which worked absolutely fine until few days ago. Now, the same documentation has been revised or modified on 05/19/2025, in revised version the data types are shown as NVARCHAR(MAX) in the OPENJSON examples. Moreover, this documentation did not have any limitations specified with usage of JSON datatype with OPENJSON. I could not find the article history to pin point or substantiate my claims. Definitely, Microsoft documentations are lacking proper updates for the recent releases/ updates being done to the products. I have seen this happening multiple times, we get into calls with Microsoft Support and they mention that the documentation has to be updated. In most of the cases we reported the issues in the documentation.

Anyways, Converting the JSON to NVARCHAR(MAX) worked for me as well. Posting my source code before and after the fix.

My original Source CODE :

DECLARE @Records JSON;
Select @Records = Records from T1;
SELECT *
FROM OPENJSON(@Records) WITH (
    TimeGenerated NVARCHAR(500) '$.TimeGenerated',
    Entity  NVARCHAR(50) '$.Context.Entity',
    Context  NVARCHAR(MAX)  '$.Context' as JSON,
    Category NVARCHAR(500) '$.Category',
    CorrelationId NVARCHAR(500) '$.CorrelationId',
    Logger NVARCHAR(500) '$.Logger',
    Message NVARCHAR(500) '$.Message',
    Method NVARCHAR(500) '$.Method', 
    StackTrace NVARCHAR(MAX) '$.StackTrace',
    Status NVARCHAR(500) '$.Status'
);

My source CODE after fixing the error :

DECLARE @Records NVARCHAR(MAX); --Changed the datatype
Select @Records = Records from T1;
SELECT *
FROM OPENJSON(@Records) WITH (
    TimeGenerated NVARCHAR(500) '$.TimeGenerated',
    Entity  NVARCHAR(50) '$.Context.Entity',
    Context  NVARCHAR(MAX)  '$.Context' as JSON,
    Category NVARCHAR(500) '$.Category',
    CorrelationId NVARCHAR(500) '$.CorrelationId',
    Logger NVARCHAR(500) '$.Logger',
    Message NVARCHAR(500) '$.Message',
    Method NVARCHAR(500) '$.Method', 
    StackTrace NVARCHAR(MAX) '$.StackTrace',
    Status NVARCHAR(500) '$.Status'
);

Collectives™ on Stack Overflow

JSON data type cannot be used in OpenJson function

2 Answers 2

Limitations

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Limitations

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related