SQL JSON_VALUE/JSON_QUERY 从数组转为行

Question

我想从 Microsoft SQL Server 数据库中 JSON 字符串内的数组中提取信息。
如果我有一个这样的 JSON 对象:

CREATE TABLE myTable([Id] int, [JsonInfo] varchar(max));

INSERT INTO myTable  ([Id], [JsonInfo])
VALUES (1, 
    '{
      "$id": "1",
      "Id": "32766177-18c7-4c2d-bbb5-02588a73ff72",
      "Metadata": [
        {
          "Identifier": "Identifier1",
          "Value": "aaa"
        },
        {
          "Identifier": "Identifier2",
          "Value": "bbb"
        },
        {
          "Identifier": "Identifier3",
          "Value": "ccc"
        },
      ],
    }'
);

我发现访问整个数组的唯一方法是:

SELECT Id, value AS Metadata
FROM myTable t
CROSS APPLY OPENJSON( JSON_QUERY(t.JsonInfo, '$.Metadata'))

为了访问数组中的信息，我执行了以下操作:

SELECT 
    JSON_VALUE(Metadata, '$.Identifier') AS Identifier,
    JSON_VALUE(Metadata, '$.Value') AS Value
FROM
    (SELECT Id, value AS Metadata
     FROM myTable t
     CROSS APPLY OPENJSON( JSON_QUERY(t.JsonInfo, '$.Metadata'))
    );

我的结果是:

Identifier  | Value
------------+--------
Identifier1 | aaa
Identifier2 | bbb
Identifier3 | ccc

结果我应该是:

Identifier1 | Identifier2 | Identifier3
------------+-------------+------------
aaa         | bbb         | ccc

不匹配记录的结果:

Identifier1 | Identifier2 | Identifier3 |Identifier4
------------+-------------+-------------+-----------
aaa         | bbb         | ccc         | NULL
aaa         | bbb         | NULL        | ddd

我知道我可以用 PIVOT 转置它，但对于这种情况来说似乎太复杂了。
关于如何轻松实现这一目标的任何建议？

Answer 1

注:您提供的 JSON 无效。我不得不为我的例子修改它。如果您的 JSON 示例是您的生产数据的样子，那么您还有其他问题需要解决。
最简单的方法是对您的数据进行 PIVOT，如下所示:

DECLARE @myTable TABLE ( [Id] INT, [JsonInfo] VARCHAR(MAX) );

INSERT INTO @myTable ( [Id], [JsonInfo] )
VALUES (1, 
    '{
      "$id": "1",
      "Id": "32766177-18c7-4c2d-bbb5-02588a73ff72",
      "Metadata": [
        {
          "Identifier": "Identifier1",
          "Value": "aaa"
        },
        {
          "Identifier": "Identifier2",
          "Value": "bbb"
        },
        {
          "Identifier": "Identifier3",
          "Value": "ccc"
        },
        {
          "Identifier": "Identifier4",
          "Value": ""
        }
      ]
    }'
);

SELECT
    Id, md.*
FROM @myTable AS mt
OUTER APPLY (

    SELECT * FROM (

        SELECT Identifier, [Value] FROM OPENJSON ( JsonInfo, '$.Metadata' ) WITH (
            Identifier VARCHAR(50) '$.Identifier',
            [Value] VARCHAR(50) '$.Value'
        )

    ) AS ids
    PIVOT (
        MAX ( [Value] ) FOR Identifier IN ( [Identifier1], [Identifier2], [Identifier3], [Identifier4] )
    ) AS pvt

) AS md;

退货

+----+-------------+-------------+-------------+-------------+
| Id | Identifier1 | Identifier2 | Identifier3 | Identifier4 |
+----+-------------+-------------+-------------+-------------+
|  1 | aaa         | bbb         | ccc         |             |
+----+-------------+-------------+-------------+-------------+

这种方法仅在您知道每个标识符的预期值和行数时才有效，而且我怀疑是否是您已经提到的情况 PIVOT .因此，您将被迫使用动态 SQL 方法。
我不完全理解您的需求，因为我不确定您的示例中不匹配的数据来自哪里，但是，继续上面的相同设置，您可以尝试以下操作:

-- Declare a variable to hold the dynamic SQL statement.
DECLARE @sql VARCHAR(MAX) = ''; -- must be initialized as an empty string.

-- Using the SQL hack to append continuous rows to a variable, we can build a list
-- of column names and their values from your JSON.
SELECT
    @sql = @sql + ', NULLIF ( ''' + md.[Value] + ''', '''' ) AS [' + md.Identifier + ']'
FROM @myTable AS mt
OUTER APPLY (

    SELECT 
        Identifier, [Value] 
    FROM OPENJSON ( JsonInfo, '$.Metadata' ) WITH (
        Identifier VARCHAR(50) '$.Identifier',
        [Value] VARCHAR(50) '$.Value'
    ) AS ids

) AS md
WHERE mt.Id = 1; -- <= note the record restriction.

-- Complete the dynamic SQL statement by adding SELECT and trimming
-- off the prefixed ", " from the column/value list by using STUFF.
SET @sql = 'SELECT ' + STUFF ( @sql, 1, 2, '' ) + ';';

-- Execute the dynamic statement.
EXEC ( @sql );

退货

+-------------+-------------+-------------+-------------+
| Identifier1 | Identifier2 | Identifier3 | Identifier4 |
+-------------+-------------+-------------+-------------+
| aaa         | bbb         | ccc         | NULL        |
+-------------+-------------+-------------+-------------+

如果您要 PRINT已完成 @sql 你会看到的变量:

SELECT NULLIF ( 'aaa', '' ) AS [Identifier1], NULLIF ( 'bbb', '' ) AS [Identifier2], NULLIF ( 'ccc', '' ) AS [Identifier3], NULLIF ( '', '' ) AS [Identifier4];

希望这有助于您继续前进。