Json_To_NodeTable
Returns tabular hierarchical data representation of a JSON string
- Free Function
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
SELECT *
FROM SQLHTTP.net.Json_To_NodeTable( @Json )
| Name | Type | Description |
|---|---|---|
| @Json | nvarchar(MAX) | Json string to be shredded into a table |
See NodeTable for detailed table structure documentation
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
DECLARE @Json nvarchar(MAX) SET @Json = '{ "firstName": "John", "lastName": "Smith", "isAlive": true, "age": 25, "address": { "streetAddress": "21 2nd Street", "city": "New York", "state": "NY", "postalCode": "10021-3100" }, "phoneNumbers": [ { "type": "home", "number": "212 555-1234" }, { "type": "office", "number": "646 555-4567" }, { "type": "mobile", "number": "123 456-7890" } ], "children": [], "spouse": null }' SELECT * FROM SQLHTTP.net.Json_To_NodeTable(@Json) |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
RowID ParentRowID NodePath JsonType Name Value -------- -------------- ----------------------------------------- ---------- --------------- -------------- 1 0 NoName Object NULL {"address":... 2 1 NoName/address{1} Object address {"city": "N... 3 2 NoName/address{1}/city String city New York 4 2 NoName/address{1}/postalCode String postalCode 10021-3100 5 2 NoName/address{1}/state String state NY 6 2 NoName/address{1}/streetAddress String streetAddress 21 2nd Street 7 1 NoName/age Number age 25 8 1 NoName/children{1} Array children [] 9 1 NoName/firstName String firstName John 10 1 NoName/isAlive Boolean isAlive true 11 1 NoName/lastName String lastName Smith 12 1 NoName/phoneNumbers{1} Array phoneNumbers [{"number":... 13 12 NoName/phoneNumbers{1}/NoName{1} Object NULL {"number": ... 14 13 NoName/phoneNumbers{1}/NoName{1}/number String number 212 555-1234 15 13 NoName/phoneNumbers{1}/NoName{1}/type String type home 16 12 NoName/phoneNumbers{1}/NoName{2} Object NULL {"number": ... 17 16 NoName/phoneNumbers{1}/NoName{2}/number String number 646 555-4567 18 16 NoName/phoneNumbers{1}/NoName{2}/type String type office 19 12 NoName/phoneNumbers{1}/NoName{3} Object NULL {"number": ... 20 19 NoName/phoneNumbers{1}/NoName{3}/number String number 123 456-7890 21 19 NoName/phoneNumbers{1}/NoName{3}/type String type mobile 22 1 NoName/spouse Null spouse NULL |