6

I want to extract a complete list of the node names and their paths from any arbitrary, well-formed JSON document stored as nvarchar value in SQL Server 2016. Is there a reliable way to do this?

For Example, for a json value:

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

Get this result upon querying @json_doc:

NODE_NAME
$.Name1
$.Name2
0

3 Answers 3

9

Select [key] from default OPENJSON schema.

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

SELECT [key]
FROM OPENJSON(@json_doc);
GO
| key   |
| :---- |
| Name1 |
| Name2 |

dbfiddle here

7

To add to this answer.

Get all JSON keys in a column:

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

E.g., Table:

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

Query Result:

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

Get all JSON keys where JSON is array of objects:

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

Table:

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

Query Result:

+--------+
| Result |
+========+
| myKey  |
+--------+
| myKey2 |
+--------+
| myKey3 |
+--------+
1

Using recursive CTE to show everything. Type 4, and 5 (results from OPENJSON function) are arrays and sub_json respectively. Others types are atomic values. Query returns full path, value, key and type of each element in json variable.

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;

On original question will return:

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

If you use numbers as keys like: SET @json_doc ='{"1":"Value1", "2":"Value2"}'; Results will be wrong:

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

Problem here is I use IIF(TRY_CAST([Key] AS int) to identify array index and no named items. Some tricks can be done to prevent it, but I don't wanted overcomplicate this thing. (And using numbers as Key names is a bad idea in my opinion). On a little more complicated example (from 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()"}
    ]
  }
}}';

results (omiting value for the sake of clarity):

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.