我有一个在这里显示的SON，存储在SQL服务器表的一列中:
{
"Name": "oxygen",
"StartDate": "2018-06-05",
"EndDate": "2018-06-22",
"Desc": {
"quality": "better",
"level,amount": "Scheduled",
"05": "carbon",
"GDP": "Max",
"CDF": "ZENE 1",
"OTBC": "REMO - MIXED UNIQUE"
}
}
如何使用T-SQL查询查找第5个密钥对值？
我try 过这个:
select JSON_QUERY(column_value,'$.Desc[5]') AS 'result'
from table_name
select JSON_VALUE([column_value],'$.Desc[5]') as'result'
from table_name
预期结果为"CDF": "ZENE 1".
感谢任何帮助.
不确定是否有简单的方法，因为OpenJNSON并不是为json对象报告的键顺序，但这里有一个 idea :
SELECT *
FROM (
SELECT ROW_NUMBER() OVER(partition BY ID ORDER BY charindex('"' + [key] + '":', column_value)) AS sort
, *
FROM (
SELECT N'{
"Name": "oxygen",
"StartDate": "2018-06-05",
"EndDate": "2018-06-22",
"Desc": {
"quality": "better",
"level,amount": "Scheduled",
"05": "carbon",
"GDP": "Max",
"CDF": "ZENE 1",
"OTBC": "REMO - MIXED UNIQUE"
}
}' AS column_value, 1 AS id
) x
CROSS apply openjson(column_value, '$.Desc')
) x
WHERE sort = 5
按照Desc keys拆分zone_Value后，我使用ROW_$判断json字符串内每个key字符串的位置，然后将其用作返回第五个值的计数器的基.
这种方法的缺点是它使用字符串方法，这种方法可能很慢并且有些脆弱