Nested JSON element parsing to NULL in Synapse Serverless SQL

August 10, 2023

Why does Outcomes parse to NULL in the code below? I’m running this on an Azure Synapse serverless using SSMS. I’ve also tried this on an Azure Managed Instance, both parse the nested array element Outcomes as NULL.

DECLARE @json VARCHAR(MAX);

SET @json = '[{"EntityId":25023,"MethodId":1,"Outcomes":[{"OutcomeId":50043,"OutcomeTypeId":1},{"OutcomeId":50044,"OutcomeTypeId":2}]}]';

SELECT *
FROM OPENJSON(@json) WITH (
    EntityId INT 'strict $.EntityId',
    MethodId INT '$.MethodId',
    Outcomes VARCHAR(MAX) '$.Outcomes',
    OutcomeId INT '$.Outcomes.OutcomeId'
    );

>Solution :

I can only test this in SQL Server, not Synapse, but the syntax should be the same.

The first issue is that you need to tell SQL Server that Outcomes is JSON data (again), by passing AS JSON. When you do this, however, you’ll get a different error:

Msg 13618, Level 16, State 1, Line 5
AS JSON option can be specified only for column of nvarchar(max) type in WITH clause.

This is because you’re using the wrong data type; JSON in SQL Server is an nvarchar(MAX), so you need to ensure you use that.

After you fix that, you also then need to use a second call to OPENJSON for the values within Outcomes:

DECLARE @json nvarchar(MAX);

SET @json = N'[{"EntityId":25023,"MethodId":1,"Outcomes":[{"OutcomeId":50043,"OutcomeTypeId":1},{"OutcomeId":50044,"OutcomeTypeId":2}]}]';

SELECT J.EntityId,
       J.MethodId,
       J.Outcomes,
       O.OutcomeId
FROM OPENJSON(@json)
         WITH (EntityId int 'strict $.EntityId',
               MethodId int,
               Outcomes nvarchar(MAX) AS JSON) J
     CROSS APPLY OPENJSON(J.Outcomes)
         WITH (OutcomeId int) O;

As you are aliases the same value as the attribute as well, I remove the clauses like $.MethodId as they are redudant.