3

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
6

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

4

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 |
+--------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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