patternMinor
Can SQL Server 2016 extract node names from JSON?
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:
Get this result upon querying @json_doc:
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
$.Name2Solution
Select [key] from default OPENJSON schema.
| key |
| :---- |
| Name1 |
| Name2 |
dbfiddle here
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);
GOContext
StackExchange Database Administrators Q#168303, answer score: 9
Revisions (0)
No revisions yet.