英文:
Using OPENJSON in SQL Server to parse a Non-Array Object
问题
我正在使用SQL Server v15，从.NET应用程序调用。
我使用的网站（不是我的 - 我不控制数据）有一个格式奇特的JSON数据集。它不是一个数组，而是一个对象，每个元素都以其ID命名：
{ "1": { "id": "1", "Name": "Charlie" }, "2": { "id": "2", "Name": "Sally" } }
我知道如何使用OPENJSON从数组中读取数据，但是否可能解析这种格式？或者最好的方法是编写一个脚本逐个遍历对象？
英文:
I'm using SQL Server v15, called from a .NET application.
A website I'm using (not mine - I don't control the data) has a JSON dataset formatted strangely. Instead of being an array like:
[{"id":"1","Name":"Charlie"},{"id":"2","Name"="Sally"}]
It's an object with each element named as its ID:
{"1":{"id":"1","Name":"Charlie"}, "2":{"id":"2","Name"="Sally"}}
I know how to use the OPENJSON to read data from an array, but is it possible to have it parse this format? Or is my best bet to have a script loop through the objects one at a time?
答案1
得分: 1
请尝试以下解决方案。
SQL
DECLARE @json NVARCHAR(MAX) =
N'{
"1": {
"id": "1",
"Name": "Charlie"
},
"2": {
"id": "2",
"Name": "Sally"
}
}';
SELECT rs.*
FROM OPENJSON (@json) AS seq
CROSS APPLY OPENJSON(seq.value)
WITH
(
[id] INT '$.id',
[Name] VARCHAR(20) '$.Name'
) AS rs;
Output
| id | Name |
|---|---|
| 1 | Charlie |
| 2 | Sally |
英文:
Please try the following solution.
SQL
DECLARE @json NVARCHAR(MAX) =
N'{
"1": {
"id": "1",
"Name": "Charlie"
},
"2": {
"id": "2",
"Name": "Sally"
}
}';
SELECT rs.*
FROM OPENJSON (@json) AS seq
CROSS APPLY OPENJSON(seq.value)
WITH
(
[id] INT '$.id'
, [Name] VARCHAR(20) '$.Name'
) AS rs;
Output
| id | Name |
|---|---|
| 1 | Charlie |
| 2 | Sally |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论