sql-server - 如何使用 FOR JSON PATH 将带有键/值对的表查询到 JSON 对象中？

Question

我有一个 Assets 表和一个属性表，其中的属性是简单的键/值对。

DECLARE @Asset TABLE(AssetID INT)
INSERT @Asset VALUES (1)

DECLARE @Att TABLE (AssetID INT, Name NVARCHAR(100), Val NVARCHAR(100))
INSERT @Att VALUES (1, 'height', '100px'), (1, 'width', '200px')

我想编写一个按 Assets 分组的查询，并包含一个包含所有属性的 JSON 表示的列。例如:

AssetID      Attributes
------------ -----------------------------------------------
1            {"height":"100px","width":"200px"}

如何编写查询以使属性名称值成为生成的 JSON 对象中的键？当我使用 FOR JSON PATH 时，键是列名:

SELECT
    AssetID,
    (
        SELECT Name, Val
        FROM @Att att
        WHERE att.AssetID = asset.AssetID
        FOR JSON PATH
    ) Attributes
FROM @Asset asset

返回...

AssetID      Attributes
------------ -----------------------------------------------
1            [{"Name":"height","Val":"100px"},{"Name":"width","Val":"200px"}]

Answer 1

我不确定任何本地人JSON获取列数据为Key的方法在 JSON . Alias名称将转换为 JSON 中的键值.

所以这是我的尝试

您需要对数据进行透视以获得 JSON 中所需的键值对格式。

如果 key那么是静态的

SELECT
    AssetID,
    (
        SELECT Max(CASE WHEN NAME = 'height' THEN Val END) AS height,
               Max(CASE WHEN NAME = 'width' THEN Val END) AS width
        FROM @Att att
        WHERE att.AssetID = asset.AssetID
        FOR JSON path, WITHOUT_ARRAY_WRAPPER
    ) Attributes
FROM @Asset asset

WITHOUT_ARRAY_WRAPPER 是删除 JSON 周围的方括号 FOR JSON 的输出默认条款

结果:

+---------+--------------------------------------+
| AssetID |              Attributes              |
+---------+--------------------------------------+
|       1 | [{"height":"100px","width":"200px"}] |
+---------+--------------------------------------+

由于键可以是我们需要使用动态查询来透视数据的任何内容

对于演示，我已将表变量更改为临时表

CREATE TABLE #Asset
  (
     AssetID INT
  )

INSERT #Asset
VALUES (1)

CREATE TABLE #Att
  (
     AssetID INT,
     NAME    NVARCHAR(100),
     Val     NVARCHAR(100)
  )

INSERT #Att
VALUES (1,'height','100px'),
       (1,'width','200px')

DECLARE @col VARCHAR(8000)= ''

SET @col = (SELECT ',Max(CASE WHEN NAME = ''' + NAME
                   + ''' THEN Val END) as ' + Quotename(NAME)
            FROM   #Att
            FOR xml path(''))
SET @col = Stuff(@col, 1, 1, '')

EXEC ('
SELECT
    AssetID,
    (
        SELECT '+@col+'
        FROM #Att att
        WHERE att.AssetID = asset.AssetID
        FOR JSON path, WITHOUT_ARRAY_WRAPPER
    ) Attributes
FROM #Asset asset') 

结果:

+---------+--------------------------------------+
| AssetID |              Attributes              |
+---------+--------------------------------------+
|       1 | [{"height":"100px","width":"200px"}] |
+---------+--------------------------------------+