将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

谢谢！

- onji

Answer 1

这里有些东西，至少可以让你朝着正确的方向开始。

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

目前让我感到困惑的一件事是缺失的值（结果中的值为空）...

我在考虑可能要做一种类似于外部/完全连接的操作，但即使这样也仍然令我头痛。你确定你需要那个吗？或者，你能否从上述SQL的输出中进行存在性检查？

我会继续尝试解决这个问题。如果我找到一个与您的输出完全匹配的解决方案，我将添加到这个答案中。

在那之前……祝你好运！

- R. Richards

---

嘿，R.Richards，太好了。我可能能够在没有NULL的情况下完成这个，但如果不行的话，请检查它是否存在并插入另一行。非常感谢！ - onji

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中查询它们，那么这很可能是一种成本较低的方法。

- ornsio