将 OPENJSON 应用于单个列

Question

我有一个包含两个属性列和一个 json 列的产品表。我希望能够分隔 json 列并插入保留属性的额外行。示例数据如下所示：

ID          Name          Attributes
1           Nikon        {"4e7a":["jpg","bmp","nef"],"604e":["en"]}
2           Canon        {"4e7a":["jpg","bmp"],"604e":["en","jp","de"]}
3           Olympus      {"902c":["yes"], "4e7a":["jpg","bmp"]}

我知道 OPENJSON 可以将 JSON 对象转换为行，将键值转换为单元格，但我如何将它应用于包含 JSON 数据的单个列？

我的目标是有一个这样的输出：

ID          Name          key        value
1           Nikon         902c       NULL
1           Nikon         4e7a       ["jpg","bmp","nef"]
1           Nikon         604e       ["en"]
2           Canon         902c       NULL
2           Canon         4e7a       ["jpg","bmp"]
2           Canon         604e       ["en","jp","de"]
3           Olympus       902c       ["yes"]
3           Olympus       4e7a       ["jpg","bmp"]
3           Olympus       604e       NULL

有没有办法像这样查询这个产品表？或者有没有办法重现我的目标数据集？

SELECT
  ID,
  Name,
  OPENJSON(Attributes)
FROM products

谢谢！

Answer 1

这里有一些东西至少会让你朝着正确的方向前进。

SELECT P.ID, P.[Name], AttsData.[key], AttsData.[Value]
FROM products P CROSS APPLY OPENJSON (P.Attributes) AS AttsData

现在让我有点卡住的一件事是缺少的值(结果值为空(......

我在想也许做一些外部/完全连接回到这个，但即使这样也让我头疼。你确定你需要那个吗？或者，您可以使用上述SQL的输出进行存在性检查吗？

我会继续这样做。如果我找到与您的输出完全匹配的解决方案，我将添加到此答案中。

在那之前...祝你好运！

Answer 2

可以通过创建可能键的列表并使用 CROSS APPLY 将每个键关联到原始数据集中的每一行，然后在解析的 JSON 中进行左联接来获取具有 NULL value字段的行。

下面是一个您应该能够按原样执行的工作示例：

-- Throw together a quick and dirty CTE containing your example data
WITH OriginalValues AS (
    SELECT *
    FROM (
        VALUES ( 1, 'Nikon', '{"4e7a":["jpg","bmp","nef"],"604e":["en"]}' ),
        ( 2, 'Canon', '{"4e7a":["jpg","bmp"],"604e":["en","jp","de"]}' ),
        ( 3, 'Olympus', '{"902c":["yes"], "4e7a":["jpg","bmp"]}' )
    ) AS T ( ID, Name, Attributes )
),
-- Build a separate dataset that includes all possible 'key' values from the JSON.
PossibleKeys AS (
    SELECT DISTINCT A.[key]
    FROM OriginalValues CROSS APPLY OPENJSON( OriginalValues.Attributes ) AS A
),
-- Get the existing keys and values from the JSON, associated with the record ID
ValuesWithKeys AS (
    SELECT OriginalValues.ID, Atts.[key], Atts.Value
    FROM OriginalValues CROSS APPLY OPENJSON( OriginalValues.Attributes ) AS Atts
)
-- Join each possible 'key' value with every record in the original dataset, and
-- then left join the parsed JSON values for each ID and key
SELECT OriginalValues.ID, OriginalValues.Name, KeyList.[key], ValuesWithKeys.Value
FROM OriginalValues
CROSS APPLY PossibleKeys AS KeyList
LEFT JOIN ValuesWithKeys
    ON OriginalValues.ID = ValuesWithKeys.ID
    AND KeyList.[key] = ValuesWithKeys.[key]
ORDER BY ID, [key];

如果您需要包含一些预先确定的key值，其中一些值可能不存在于存储在Attributes中的任何 JSON 值中，您可以构造一个 CTE(就像我模拟您的原始数据集所做的那样(或临时表来提供这些值，而不是在上面的 PossibleKeys CTE 中进行 DISTINCT 选择。 如果您已经知道可能的key值是什么，而不必从 JSON 中查询它们，那么这很可能是一种成本较低的方法。