SQL-JSON单元格查询

Question

我是JSON-Code的新手，我在SQL Server中有一个单元格，它来自JSON格式的数据库。

以下是数据库中的JSON字符串：

{
"fields":{
"field":[
{
"result":0,
"isrequired":"true",
"name":"empfohlene Garantiebewertung",
"type":"eEnumeration",
"facets":[
{
"facet":[
{
"id":0,
"type":"Enumeration",
"value":"keine Garantie;Garantie ;Kulanz"
},
{
"id":1,
"type":"choicetype",
"value":"singlechoice"
},
{
"id":2,
"type":"ErrorRange",
"value":"false;false;false"
}
]
}
]
}
]
}
}

结果0告诉；keine Garantie"；已在我们的数据库中选定。我可以直接显示在SQL查询中已选择值的一个单元格中吗？

下面是一个我用SQL知识可以得到的最佳结果的例子：

SELECT
JSON_VALUE (REPLACE(Facet.[value], '[', ''), '$.facet.value') as 'Values',
Result.[value] as 'Result'
FROM
CROSS APPLY OPENJSON(JSON_query(JSONCELL, '$.fields.field'))  as Result2
CROSS APPLY OPENJSON(Result2.[value], '$')  as Result
CROSS APPLY OPENJSON(JSON_query(JSONCELL, '$.fields.field'))  as Facet2
CROSS APPLY OPENJSON(Facet2.[value], '$')  as Facet

结果：

值"keine Garantie；Garantie；Kulanz"；

结果"0"；

编辑：Im在结果之后"；keine Garantie"；。在数据来源的网站上，我可以从"；keine Garantie"Garantie"Kulanz"；。结果0表明用户选择了"0"；keine Garantie"；。

提前感谢您的帮助和回答！

Answer 1

这就是你想要的吗？

DECLARE @JSON nvarchar(MAX) = N'{
"fields":{
"field":[
{
"result":0,
"isrequired":"true",
"name":"empfohlene Garantiebewertung",
"type":"eEnumeration",
"facets":[
{
"facet":[
{
"id":0,
"type":"Enumeration",
"value":"keine Garantie;Garantie ;Kulanz"
},
{
"id":1,
"type":"choicetype",
"value":"singlechoice"
},
{
"id":2,
"type":"ErrorRange",
"value":"false;false;false"
}
]
}
]
}
]
}
}';
SELECT F.result,
FFF.[value]
FROM (VALUES(@JSON))V(J)
CROSS APPLY OPENJSON(V.J,'$.fields.field') 
WITH (result int,
facets nvarchar(MAX) AS JSON) F
CROSS APPLY OPENJSON(F.facets)
WITH (facet nvarchar(MAX) AS JSON) FF
CROSS APPLY OPENJSON(FF.facet)
WITH(id int,
value nvarchar(4000)) FFF
WHERE F.result = 0
AND FFF.id = 0;