SQL以特定格式生成JSON-有解無憂

我正在使用 MS SQL。我有這張表：

SELECT id, description, contacts, type FROM Table1 WHERE id = 'AA'

輸出：

id  description        contacts  type
AA  Albany Associates  12        static
AA  Albany Associates  23        static
AA  Albany Associates  13        static
AA  Albany Associates  45        static
AA  Albany Associates  23        static
AA  Albany Associates  43        static

任何人都請幫助我應該如何撰寫 SQL 以獲得這樣的 JSON 輸出：

{"id":"AA",
"description":"Albany Associates",
"members":
    {
        "contacts":
                    ["12","23","13","45","43","23","43"]},
        "type":     "Static"
    }

我試過這個：

    SELECT id, description, contacts, type FROM Table1 WHERE id = 'AA' FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER

輸出是這樣的：

 {"id":"AA",
"description":"Albany Associates",
"contact_1004_contactID":"12",
"type":"static"}
,{"id":"AA",
"description":"Albany Associates",
"contact_1004_contactID":"23",
"type":"static"}

uj5u.com熱心網友回復：

設定：

SELECT *
INTO Table1
FROM (VALUES
('AA','Albany Associates','12','static'),
('AA','Albany Associates','23','static'),
('AA','Albany Associates','13','static'),
('AA','Albany Associates','45','static'),
('AA','Albany Associates','23','static'),
('AA','Albany Associates','43','static')) 
T(id, description, contacts, [type])

詢問：

SELECT 
    id,
    description, 
    JSON_QUERY(
        (SELECT '['  
            STRING_AGG('"'   STRING_ESCAPE(contacts, 'json')   '"', ',')  
         ']')) AS "members.contacts",
    [type] AS "type"
FROM Table1
WHERE id = 'AA'
GROUP BY id, description, [type]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

輸出（漂亮的列印，SQL Server 本身不這樣做）：

{
    "id": "AA",
    "description": "Albany Associates",
    "members": {
        "contacts": ["12", "23", "13", "45", "23", "43"]
    },
    "type": "static"
}

請注意我們必須構建字串陣列的笨拙方式。不幸的是，T-SQL 沒有用于構建 JSON 標量陣列的本機函式。鑒于輸入我們并不嚴格需要包含STRING_ESCAPE呼叫（因為沒有特殊字符），但無論如何包含它會更安全，以防萬一。

SQL Server 2016 沒有STRING_AGG，所以這變得更加丑陋：

SELECT 
    id,
    description, 
    JSON_QUERY('['   STUFF(
        (
            SELECT ',"'   STRING_ESCAPE(contacts, 'json')   '"'
            FROM Table1 T2 
            WHERE T2.id = T1.id
            FOR XML PATH(''), TYPE
        ).value('text()[1]', 'NVARCHAR(MAX)'), 1, 1, '') 
      ']') AS "members.contacts",
    [type] AS "type"
FROM Table1 T1
WHERE id = 'AA'
GROUP BY id, description, [type]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

現在我們需要同時考慮 XML 和 JSON 轉義，這很有趣。這個查詢假設id是足夠獨特的加入，這恰好是這個特定表的情況。

轉載請註明出處，本文鏈接：https://www.uj5u.com/net/438188.html

標籤：sql json sql服务器

上一篇：LinuxJQ。如何從特定ID條目中提取資料

下一篇：從json到物件飛鏢