在Synapse无服务器SQL中将嵌套的JSON元素解析为NULL。

huangapple go评论71阅读模式
英文:

Nested JSON element parsing to NULL in Synapse Serverless SQL

问题

Outcomes 在下面的代码中为什么解析为 NULL?我在 Azure Synapse 服务器上使用 SSMS 运行这段代码。我还尝试在 Azure 管理实例上运行,结果都将嵌套数组元素 Outcomes 解析为 NULL。

英文:

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'
    );

答案1

得分: 1

我只能在SQL Server中测试这个问题,而不是在Synapse中,但语法应该是相同的。

第一个问题是你需要再次告诉SQL Server Outcomes 是JSON数据,通过添加 AS JSON。然而,当你这样做时,你会得到一个不同的错误:
> Msg 13618,级别 16,状态 1,第 5 行
> AS JSON 选项只能在 WITH 子句中指定 nvarchar(max) 类型的列。

这是因为你使用了错误的数据类型;在SQL Server中,JSON是 nvarchar(MAX),所以你需要确保你使用这个数据类型。

在你修复这个问题后,你还需要使用第二个 OPENJSON 调用来获取 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;

由于你将属性别名为相同的值,我删除了像 $.MethodId 这样的子句,因为它们是多余的。

英文:

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.

huangapple
  • 本文由 发表于 2023年8月10日 21:04:05
  • 转载请务必保留本文链接:https://go.coder-hub.com/76875990.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定