英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论