在我的資料庫中，我有一個名為library的表，其中包含依賴項、電子郵件、警報、跳過等列，并且值存盤為
declare @library table (dependencies varchar(max),email varchar(max),alert varchar(25),skipped varchar(25) )
insert into @library values('{"jar":"azure.com","maven":"azure.com","maven":"azure.com"}','[email protected],[email protected]',true,false)
我想準備這些值的 JSON，看起來像
{
"libraries":[
{
"jar" : { "coordinates":"azure.com"}
},
{
"maven" : {"coordinates":"azure.com" }
},
{
"maven" : {"coordinates":"azure.com" }
}
],
"email_test":{
"email": [
"[email protected]",
"[email protected]"
],
"alert" : true,
"skipped": false
}
}
我寫的代碼為
select (select dependencies as [maven.coordinates] for json path) as libraries,
(select email from library for json auto) as [email_test.email],
alert as [email_test.alert],
skipped as [email_test.skipped]
from library for json path, WITHOUT_ARRAY_WRAPPER
而且我的 JSON 看起來不像想要的 JSON
{
"libraries":[
{
"maven":{
"coordinates":"{"jar":"azure.com","maven":"azure.com"}"
}
}
],
"email_test":{
"email":[
{
"email":"[email protected] , [email protected]"
}
],
"alert":"true",
"skipped":"false"
}
}
我無法實作正確的 json 。有人可以幫我嗎？
謝謝
uj5u.com熱心網友回復：
您現有的查詢存在許多問題：
dependencies包含整個coordinates資料，您不能只選擇整個資料。相反，你需要打破它并重建它。你可以OPENJSON像我一樣使用，或者JSON_VALUEemail不是有效的 JSON，它是一個逗號分隔的串列。不幸的是，SQL Server 沒有JSON_AGG使這更簡單的方法。因此，使用（以轉義任何有問題的字符）和（再次聚合它）和（以防止雙重轉義）的組合將其打開STRING_SPLIT并重建。STRING_ESCAPESTRING_AGGJSON_QUERYtrue并且false似乎是varchar，您需要一個bit值
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,
JSON_QUERY((
SELECT '[' STRING_AGG('"' STRING_ESCAPE(value, 'json') '"', ',') ']'
FROM STRING_SPLIT(l.email, ',')
)) as [email_test.email],
CASE WHEN l.alert = 'true' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END as [email_test.alert],
CASE WHEN l.skipped = 'true' THEN CAST(1 AS bit) ELSE CAST(0 AS bit) END as [email_test.skipped]
FROM @library l
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;
db<>小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/451032.html