我的資料庫表創建為：
declare @library table (dependencies varchar(max))
insert into @library values('{"jar":"azure.com","maven":"azure.com","maven":"azure.com","maven":"azurebook.com"}')
我正在嘗試將 JSON 作為
{
"libraries":[
{
"jar" : { "coordinates":"azure.com"}
},
{
"maven" : {"coordinates":"azure.com" }
},
{
"maven" : {"coordinates":"azurebook.com" }
}
]
}
我的 SQL 代碼看起來像
SELECT
(
select
j.jar as [jar.coordinates],
j.maven as [maven.coordinates]
FROM OPENJSON(l.dependencies)
WITH (
jar varchar(100),
maven varchar(100)
) j
FOR JSON PATH
) as libraries
FROM @library l
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
And my output which is not same as desired json
{
"libraries":[
{
"jar" : { "coordinates":"azure.com"}
},
{
"maven" : {"coordinates":"azure.com" }
}
]
}
As in my output, my other property or column maven does not including. Can somebody help me out?
Thank you
uj5u.com熱心網友回復：
您不能使用OPENJSON模式來執行此操作，因為 JSON 通常只需要唯一的屬性名稱。您可以OPENJSON在沒有模式的情況下使用，但也不能用于FOR JSON創建這樣的 JSON 值。你會STRING_AGG再次需要
SELECT
JSON_QUERY((
SELECT
'[' STRING_AGG(
'{"'
STRING_ESCAPE(j.[key], 'json')
'":"'
STRING_ESCAPE(j.value, 'json')
'"}',
','
) ']'
FROM OPENJSON(l.dependencies) j
)) as libraries
FROM @library l
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451029.html
標籤：sql json sql-server open-json
上一篇：自定義ListviewsetonItemClickListner在DialogFragment中使用kotlin中的Viewbinding不起作用