如何编写一个选择查询来从Json对象中获取索引值

Question

我有下面的JSON对象。我需要编写一个select查询来获取ObjectJSON数组的索引值。有点像获取序列值。

{
"Model": [
{
"ModelName": "Test Model",    
"Object": [
{
"ID": 1,
"Name": "ABC",
},
{
"ID": 11,
"Name": "ABCD",
},
{
"ID": 15,
"Name": "ABCDE",
},
]  
}]}

预期输出：

Index_Value
1
2
3

Answer 1

如果我正确理解了这个问题，并且您想获得ObjectJSON数组中项目的索引，那么您需要使用具有默认模式的OPENJSON()。结果是一个包含列key、value和type的表，在JSON数组的情况下，key列保存数组中每个项目的索引(基于0(：

JSON:

DECLARE @json nvarchar(max) = N'{
"Model":[
{
"ModelName":"Test Model",
"Object":[
{
"ID":1,
"Name":"ABC"
},
{
"ID":11,
"Name":"ABCD"
},
{
"ID":15,
"Name":"ABCDE"
}
]
}
]
}'

声明：

SELECT CONVERT(int, j2.[key]) + 1 AS item_id
FROM OPENJSON (@json, '$.Model') j1
CROSS APPLY OPENJSON(j1.[value], '$.Object') j2

但是，如果要获取ObjectJSON数组中ID键的值，则语句不同：

SELECT j2.ID
FROM OPENJSON (@json, '$.Model') j1
CROSS APPLY OPENJSON(j1.[value], '$.Object') WITH (
ID int '$.ID'
) j2

注意，您需要两个OPENJSON()调用，因为输入JSON具有嵌套的数组结构。当然，如果ModelJSON数组总是有一个项，则可以使用适当的path:来简化语句

SELECT CONVERT(int, [key]) + 1 AS item_id
FROM OPENJSON (@json, '$.Model[0].Object')

最后，要获得index、ID和Name，您应该使用以下语句，该语句假设$.ModelJSON数组有多个项，并使用适当的数据类型定义ID和Name列：

SELECT 
CONVERT(int, j2.[key]) + 1  AS ItemID, 
j3.ID, j3.Name
FROM OPENJSON (@json, '$.Model') j1
CROSS APPLY OPENJSON(j1.[value], '$.Object') j2
CROSS APPLY OPENJSON(j2.[value], '$') WITH (
ID int '$.ID',
Name varchar(50) '$.Name'
) j3

Answer 2

DECLARE @json nvarchar(max) = N'{
"Model":[
{
"ModelName":"Test Model",
"Object":[
{
"ID":1,
"Name":"ABC"
},
{
"ID":11,
"Name":"ABCD"
},
{
"ID":15,
"Name":"ABCDE"
}
]
}
]
}'
declare @i int=0; 
SELECT 
j2.ID, j2.Name
FROM OPENJSON (@json, '$.Model') j1
CROSS APPLY OPENJSON(j1.[value],concat('$.Object[',@i,']')) WITH (
ID i`enter code here`nt '$.ID', Name varchar(100) '$.Name'
) j2

结果：-

ID	名称
11	ABCD

您可以选择select子句中的键列，而无需在of crossconnect中提及。

SELECT 
distinct t.id,
JSON_VALUE(AttsData.[value], '$.address') as address,
JSON_VALUE(AttsData.[value], '$.name') as name,
JSON_VALUE(AttsData.[value], '$.owner_name') as owner_name,
JSON_VALUE(AttsData.[value], '$.project') as project
,CONVERT(int, AttsData.[key]) index_id
FROM mytablewithjsonfeild t
CROSS APPLY OPENJSON (t."jsonfeild",N'$.parentkey') as   AttsData

在上面的查询中，我已经从表中交叉加入了JSON字段。在select语句中，我取了特定的键。

和CCD_ 19得到元素的索引