主页 / dba / 问题 / 331386
Accepted
Peter
Asked: 2023-09-20 16:15:48 +0800 CST

根据表内容创建 JSON 元素

  • 772

使用以下数据库架构，我尝试检索与属性和指示器（如果它们已连接）组合的项目的 JSON。

我开始于：

DECLARE @Item TABLE
(
   ItemId INT,
   Name VARCHAR(25)
);

INSERT INTO @Item
(
   ItemId,
   Name
)
VALUES
(
   1,
   'Item 1'
),
(
   2,
   'Item 2'
);

DECLARE @Property TABLE
(
   PropertyId INT,
   Name VARCHAR(10)
);

INSERT INTO @Property
(
   PropertyId,
   Name
)
VALUES
(
   1,
   'Property 1'
),
(
   2,
   'Property 2'
),
(
   3,
   'Property 3'
);

DECLARE @ItemProperty TABLE
(
   ItemId INT,
   PropertyId INT
);

INSERT INTO @ItemProperty
(
   ItemId,
   PropertyId
)
VALUES
(
   1,
   1
),
(
   1,
   2
),
(
   2,
   2
),
(
   2,
   3
);

SELECT *
FROM   @Item AS i
       OUTER APPLY (
                      SELECT
                           p.Name,
                           CASE WHEN ip.PropertyId IS NOT NULL THEN
                                   CAST(1 AS BIT)
                                ELSE CAST(0 AS BIT)
                           END AS ItemExists
                      FROM @Property AS p
                           LEFT JOIN @ItemProperty AS ip ON ip.PropertyId = p.PropertyId
                                                            AND ip.ItemId = i.ItemId
                   ) a
WHERE  i.ItemId = 1
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

这给了我以下结果：

{
    "ItemId": 1,
    "Name": "Item 1",
    "a": [
        {
            "Name": "Property 1",
            "ItemExists": true
        },
        {
            "Name": "Property 2",
            "ItemExists": true
        },
        {
            "Name": "Property 3",
            "ItemExists": false
        }
    ]
}

但是，我希望得到这个结果

{
    "ItemId": 1,
    "Name": "Item 1",
    "Property 1": true,
    "Property 2": true,
    "Property 3": false
}

当我向 @Property 添加新属性时，我希望自动添加它。

我怎样才能做到这一点？

sql-server

2 个回答

  1. Best Answer

    不幸的是，SQL Server 目前不支持JSON_OBJECT_AGG，这会使这种类型的查询变得更加容易。

    STRING_AGG相反，您需要使用和手动构建 JSONSTRING_ESCAPE

    SELECT
  CONCAT(
    '{"ItemId":',
    i.ItemId,
    ',"Name":"',
    STRING_ESCAPE(i.Name, 'json'),
    '"',
    p.Properties,
    '}'
  )
FROM @Item AS i
OUTER APPLY (
    SELECT Properties =
      STRING_AGG(
        ',"' +
        STRING_ESCAPE(p.Name, 'json') +
        '":' +
        IIF(ip.ItemId IS NULL, N'false', N'true'),
        ',"'
      )
    FROM @Property AS p
    LEFT JOIN @ItemProperty AS ip
      ON ip.PropertyId = p.PropertyId
     AND ip.ItemId = i.ItemId
) p
WHERE i.ItemId = 1;

    数据库<>小提琴

    • 3

  2. 一种方法（我希望是正确的），因为你有未知数量的“Property x”，就是使用动态枢轴。请参阅此处了解更多信息。https://www.mssqltips.com/sqlservertip/6245/sql-server-dynamic-pivot-query/

    我将结果集存储到#tmp 表中

    SELECT i.ItemId, i.Name, a.Name as PropertyName, a.ItemExists
INTO #tmp
FROM   @Item AS i
       OUTER APPLY (
                      SELECT
                           p.Name,
                           CASE WHEN ip.PropertyId IS NOT NULL THEN
                                   CAST(1 AS tinyint)
                                ELSE CAST(0 AS tinyint)
                           END AS ItemExists
                      FROM @Property AS p
                           LEFT JOIN @ItemProperty AS ip ON ip.PropertyId = p.PropertyId
                                                            AND ip.ItemId = i.ItemId
                   ) a
WHERE  i.ItemId = 1;

    然后，基于动态枢轴：

     DECLARE @columns NVARCHAR (2000),
        @columnsSelect NVARCHAR(2000)=N'',
        @tsql NVARCHAR (2000),
        @unknownValsCol NVARCHAR (100) =N'PropertyName',
        @objNameToPivot NVARCHAR (100) ='#tmp',
        @aggFuncOfPivot NVARCHAR (3)=N'MAX',
        @aggColOfPivot NVARCHAR (100)=N'ItemExists',
        @leadColPivot NVARCHAR (100) =N'itemID,Name',
        @jsonExp NVARCHAR(100) =N'FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER';

   DECLARE @distinctVals TABLE (val NVARCHAR (50));

   SET @columns = N'';
   SET @tsql = CONCAT ('SELECT DISTINCT ', @unknownValsCol,' FROM ',@objNameToPivot)
   INSERT @distinctVals EXEC (@tsql);


   SELECT @columns += CONCAT ('[', Val,']',',')
   FROM @distinctVals;

   SELECT @columnsSelect += CONCAT (',CONVERT(bit,PivotTable.[', Val,']) as [', Val,']')
   FROM @distinctVals;



   SET @columns = LEFT (@columns, LEN (@columns) - 1)
   SET @tsql = CONCAT ( 'SELECT ', @leadColPivot,   @columnsSelect,' FROM ',' ( SELECT ',@leadColPivot,',',
         @aggColOfPivot,',',   @unknownValsCol,   ' FROM ',   @objNameToPivot,   ') as t ',
         ' PIVOT (', @aggFuncOfPivot,   '(', @aggColOfPivot,   ')',' FOR ',   @unknownValsCol,
         '   IN (', @columns,')) as PivotTable ',' ORDER BY ',   @leadColPivot ,' ', @jsonExp)

   EXEC (@tsql);

    输出将是：

    {
  "itemID":1,
  "Name":"Item 1",
  "Property 1":true,
  "Property 2":true,
  "Property 3":false
}

    这是硬编码版本

    SELECT itemID,Name,
    CONVERT(bit,PivotTable.[Property 1]) as [Property 1],
    CONVERT(bit,PivotTable.[Property 2]) as [Property 2],
    CONVERT(bit,PivotTable.[Property 3]) as [Property 3]
FROM
(
    SELECT itemID,Name,PropertyName,ItemExists
    FROM #tmp as t
) as src
PIVOT 
(  
  MAX(ItemExists)
  FOR PropertyName IN ([Property 1], [Property 2], [Property 3])  
) AS PivotTable  
ORDER BY itemID, Name
FOR JSON AUTO, WITHOUT_ARRAY_WRAPPER;

    dbfiddle在这里

    无枢轴...

    DECLARE @SQL AS VARCHAR(MAX);
DECLARE @WithCols VARCHAR(MAX);
DECLARE @Json VARCHAR(MAX);

SELECT i.ItemId,
        i.Name as ItemName,
        a.Name as PropertyName,
        a.ItemExists
INTO #tmp
FROM   @Item AS i
        OUTER APPLY (
                        SELECT
                            p.Name,
                            CASE WHEN ip.PropertyId IS NOT NULL THEN
                                    CAST(1 AS bit)
                                ELSE CAST(0 AS bit)
                            END AS ItemExists
                        FROM @Property AS p
                            LEFT JOIN @ItemProperty AS ip ON ip.PropertyId = p.PropertyId
                                                            AND ip.ItemId = i.ItemId
                    ) a
WHERE  i.ItemId = 1;


;WITH ColNames AS
(
    SELECT DISTINCT /*TOP(10)*/ PropertyName--,ItemExists
    FROM #tmp
)
SELECT   --TOP(10)
    --Pivoted column names that will be specified in the OPENJSON WITH clause.
    @WithCols = '"itemID" INT ''$.itemID'','  + 
                '"Name" NVARCHAR(100) ''$.Name'','  + 
        STRING_AGG('"' + CAST(PropertyName AS VARCHAR(MAX)) + '" NVARCHAR(100) ''$."' 
                        --+ CAST(PropertyName AS VARCHAR(MAX)) + '"''', ', ') 
                        + CAST(PropertyName AS VARCHAR(MAX)) + '"''', ', ') 
        WITHIN GROUP ( ORDER BY PropertyName )
FROM ColNames;



SELECT @Json = '[' + STRING_AGG(CAST(t.JsonData AS VARCHAR(MAX)), ',') + ']'
FROM
(
    SELECT 
            N'{' + '"itemID":' + CAST(src.ItemId AS VARCHAR(MAX)) + 
                   ',"Name":"' + CAST(src.ItemName AS VARCHAR(MAX)) + 
            
                COALESCE('",' + STRING_AGG('"' + CAST(src.PropertyName AS VARCHAR(MAX)) + '":"'
                                             -- + CAST(CONVERT(bit,src.ItemExists) AS VARCHAR(MAX)), '",'), '') + 
                                              + CONVERT(varchar(MAX),CONVERT(bit,src.ItemExists)), '",'), '') + 
            '"}' 
            AS JsonData
    FROM #tmp as src
    GROUP BY src.ItemId,
          src.ItemName
)as t;


--SELECT @Json
--Parse the JSON data and return a result set.
SET @SQL = '
SELECT *
FROM OPENJSON(''' + @Json + ''')
    WITH
    (
        ' + @WithCols + '
    )
';
--print @sql
EXEC(@SQL);

    dbfiddle在这里

    • 2

相关问题