主页 / dba / 问题 / 168303
Accepted
peterk411
Asked: 2017-03-28 10:51:03 +0800 CST

SQL Server 2016 可以从 JSON 中提取节点名称吗？

  • 772

我想从 SQL Server 2016 中存储为 nvarchar 值的任意格式良好的 JSON 文档中提取节点名称及其路径的完整列表。有没有可靠的方法来做到这一点？

例如，对于 json 值：

DECLARE @json_doc nvarchar(4000) = '{"Name1":"Value1", "Name2":"Value2"}'

在查询 @json_doc 时得到这个结果：

NODE_NAME
$.Name1
$.Name2
t-sql sql-server-2016

3 个回答

  1. Best Answer

    从默认的 OPENJSON 模式中选择 [key] 。

    DECLARE @json_doc nvarchar(4000) = '{"Name1":"Value1", "Name2":"Value2"}';

SELECT [key]
FROM OPENJSON(@json_doc);
GO

    | 关键 |
| :---- |
| 姓名1 |
| 姓名2 |

    dbfiddle在这里

    • 9

  2. 添加到这个答案。

    获取列中的所有 JSON 键：

    SELECT Keys.[key]
FROM dbo.<table_name> t
CROSS APPLY (
    SELECT [key] [key]
    FROM OPENJSON((SELECT t.<column_name>))
) Keys

    例如， 表：

    +------------------+
| JSONValues       |
+==================+
| {"myKey":1}      |
+------------------+
| {"myOtherKey":1} |
+------------------+

    查询结果：

    +------------+
| Result     |
+============+
| myKey      |
+------------+
| myOtherKey |
+------------+

    获取所有 JSON 键，其中 JSON 是对象数组：

    SELECT DISTINCT Keys.[key]
FROM dbo.<table_name, sysname, sample_table> t
CROSS APPLY (
    SELECT x.[value] [value]
    FROM OPENJSON((SELECT t.<json_colum_name, sysname, sample_column>)) x
) ArrayJSON
CROSS APPLY (
    SELECT [key] [key]
    FROM OPENJSON((SELECT ArrayJSON.[value])) x
) Keys

    表：

    +----------------------------+
| JSONValues                 |
+============================+
| [{"myKey":1},{"myKey2":2}] |
+----------------------------+
| [{"myKey3":3}]             |
+----------------------------+

    查询结果：

    +--------+
| Result |
+========+
| myKey  |
+--------+
| myKey2 |
+--------+
| myKey3 |
+--------+
    • 7

  3. 使用递归 CTE 显示一切。类型 4 和 5（来自 OPENJSON 函数的结果）分别是数组和 sub_json。其他类型是原子值。查询返回 json 变量中每个元素的完整路径、值、键和类型。

    WITH
T AS (   SELECT CAST(CONCAT ('$', IIF(TRY_CAST([Key] AS int) IS NOT NULL, CONCAT ('[', [Key], ']'), '.' + [Key])) AS nvarchar(MAX)) AS Path
              , [Key]
              , Value
              , Type
              , 1                                                                                                                   Lvl
         FROM OPENJSON (@json_doc)
         UNION ALL
         SELECT CAST(CONCAT (T.Path, IIF(TRY_CAST(O.[Key] AS int) IS NOT NULL, CONCAT ('[', O.[Key], ']'), '.' + O.[Key])) AS nvarchar(MAX))
              , O.[Key]
              , O.Value
              , O.Type
              , T.Lvl + 1
         FROM T
             CROSS APPLY OPENJSON (T.Value) O
         WHERE T.Type IN ( 4, 5 ))
SELECT Path, T.[Key], T.Value, T.Type, T.Lvl FROM T;

    原始问题将返回：

    Path    Key     Value   Type  Lvl
$.Name1 Name1   Value1  1     1
$.Name2 Name2   Value2  1     1

    如果您使用数字作为键，例如： SET @json_doc ='{"1":"Value1", "2":"Value2"}'; 结果会出错：

    Path    Key Value   Type    Lvl
$[1]    1   Value1  1   1
$[2]    2   Value2  1   1

    这里的问题是我使用 IIF(TRY_CAST([Key] AS int) 来识别数组索引并且没有命名项。可以采取一些技巧来防止它，但我不想让这件事过于复杂。（并使用数字作为键名在我看来是个坏主意）。在一个更复杂的例子中（来自http://www.json.org/example.html）

    DECLARE @json_doc nvarchar(4000) = '{"menu": {
  "id": "file",
  "value": "File",
  "popup": {
    "menuitem": [
      {"value": "New", "onclick": "CreateNewDoc()"},
      {"value": "Open", "onclick": "OpenDoc()"},
      {"value": "Close", "onclick": "CloseDoc()"}
    ]
  }
}}';

    结果（为清楚起见省略值）：

    Path                               Key       Type     Lvl
$.menu                             menu      5        1
$.menu.id                          id        1        2
$.menu.value                       value     1        2
$.menu.popup                       popup     5        2
$.menu.popup.menuitem              menuitem  4        3
$.menu.popup.menuitem[0]           0         5        4
$.menu.popup.menuitem[1]           1         5        4
$.menu.popup.menuitem[2]           2         5        4
$.menu.popup.menuitem[2].value     value     1        5
$.menu.popup.menuitem[2].onclick   onclick   1        5
$.menu.popup.menuitem[1].value     value     1        5
$.menu.popup.menuitem[1].onclick   onclick   1        5
$.menu.popup.menuitem[0].value     value     1        5
$.menu.popup.menuitem[0].onclick   onclick   1        5
    • 1

相关问题