HiveBrain v1.2.0
Get Started
← Back to all entries
patternMinor

Can SQL Server 2016 extract node names from JSON?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
cansqlnodejsonnamesextractserverfrom2016

Problem

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

Solution

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

Code Snippets

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

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

Context

StackExchange Database Administrators Q#168303, answer score: 9

Revisions (0)

No revisions yet.