SQL Server 2016 – Extract Node Names from JSON

jsonsql-server-2016t-sql

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

Best Answer

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

Related Question