AzureSQL：以列為鍵生成JSON

我的 sql server 表中有以下資料

我想要下面的 Json 使用 sql 查詢

{
    "lastname": {
      "value": "ccc",
      "valueHash": "yyy"
    },
    "email": {
      "value": "[email protected]",
      "valueHash": "xxx"
    },
    "firstName": {
      "value": "bbb",
      "valueHash": "www"
    },
    "country": {
      "value": "aaa",
      "valueHash": "zzz"
    }
}

我可以提出以下查詢

select Value as 'value', ValueHash as 'valueHash'  from user
where id=752594
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

哪個回傳

    {
      "value": "ccc",
      "valueHash": "yyy"
    },
    {
      "value": "[email protected]",
      "valueHash": "xxx"
    },
    {
      "value": "bbb/T1B 4nzpVhb0M",
      "valueHash": "www"
    },
    {
      "value": "aaa",
      "valueHash": "zzz"
    }

嘗試使用列值作為 json dict 鍵生成 json的解決方案，但出現編譯器錯誤。

有人可以幫我嗎？TIA

uj5u.com熱心網友回復：

不幸的是，SQL Server 沒有JSON_AGGor JSON_OBJECT_AGG。所以你需要用STRING_AGG和破解它STRING_ESCAPE

SELECT
  '{'  
  STRING_AGG(
  CONCAT(
    '"',
    STRING_ESCAPE(u.Name, 'json'),
    '":',
    v.json
  ), ','
 )   '}'
FROM [user] u
CROSS APPLY (
    SELECT
      u.Value AS value,
      u.ValueHash AS valueHash
    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) v(json)
WHERE u.id = 752594;

db<>小提琴

uj5u.com熱心網友回復：

這會產生類似的結果。告訴 SQL Server 不對內部值使用陣列包裝器會導致結果轉義：

WITH YourTable AS(
    SELECT *
    FROM (VALUES('country','aaa','zzz'),
                ('lastname','ccc','yyy'),
                ('email','[email protected]','xxx'),
                ('firstName','bbb','www'))V(Name,Value,ValueHash))
SELECT (SELECT value,
               ValueHash
        WHERE YT.Name = 'lastname'
        FOR JSON PATH) AS lastname,
       (SELECT value,
               ValueHash
        WHERE YT.Name = 'email'
        FOR JSON PATH) AS email,
       (SELECT value,
               ValueHash
        WHERE YT.Name = 'firstName'
        FOR JSON PATH) AS firstName,
       (SELECT value,
               ValueHash
        WHERE YT.Name = 'country'
        FOR JSON PATH) AS country
FROM YourTable YT
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

uj5u.com熱心網友回復：

如果您想要回傳多個物件，假設您id在表中有一個列，正如您似乎暗示您的WHERE子句，您可以執行以下操作：

DECLARE @t TABLE (id INT,
                  Name VARCHAR(100),
                  Value VARCHAR(100),
                  ValueHash VARCHAR(100));
INSERT @t (id, Name, Value, ValueHash)
VALUES (1, 'country', 'aaa', 'zzz'),
       (1, 'lastname', 'ccc', 'yyy'),
       (1, 'email', '[email protected]', 'xxx'),
       (1, 'firstName', 'bbb', 'www'),
       (2, 'country', 'aaa2', 'zzz2'),
       (2, 'lastname', 'ccc2', 'yyy2'),
       (2, 'email', '[email protected]', 'xxx2'),
       (2, 'firstName', 'bbb2', 'www2');


SELECT (SELECT Value, ValueHash
        FROM @t
        WHERE id = t1.id AND Name = 'lastname'
       FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) lastname,
       (SELECT Value, ValueHash
        FROM @t
        WHERE id = t1.id AND Name = 'email'
       FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) email,
       (SELECT Value, ValueHash
        FROM @t
        WHERE id = t1.id AND Name = 'firstName'
       FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) firstName,
       (SELECT Value, ValueHash
        FROM @t
        WHERE id = t1.id AND Name = 'country'
       FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER) country
FROM @t t1
WHERE t1.Name = 'lastname'
FOR JSON PATH;

請注意，與 Larnu 的解決方案一樣，它也會轉義 和 的Value值ValueHash。

標籤：sql json sql服务器 天蓝色 sql 数据库

上一篇：從c#中的（動態）json訪問物件json.`params`（保留關鍵字）

下一篇：將長字串決議為lat&longsFlutter