Json_To_OrdinalTable

Returns tabular data representation of a JSON string

Free Function
SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017

Syntax:

SELECT *
FROM SQLHTTP.net.Json_To_OrdinalTable( @Json )

Arguments:

Name	Type	Description
@Json	nvarchar(MAX)	JSON string to be shredded into a table



Table Returned:

Column name	Data type	Description
RowID	int	Auto-generated unique ID
ParentRowID	int	Parent RowID, if any.
NodePath	nvarchar(MAX)	Path to current position in the Json string or the XML Value. Note that this path is similar to XPath but sqaure brackets were replaced with curly brackets in order to simplify using this value in a WHERE clause
Col4	nvarchar(MAX)	JSON data of a String, Number or Boolean
Col5	nvarchar(MAX)	JSON data of a String, Number or Boolean
. . .
Col1024	nvarchar(MAX)	JSON data of a String, Number or Boolean



Remarks:

Use of this function is often preceded by executing the Json_To_GlobalTable stored procedure which will also generate a SQL query for using this function with the columns properly aliased. This function is intended for users who wish to avoid using XPath syntax (by first converting a Json string to XML using the Json_To_Xml function and then utilizing the XQueryHelper stored procedure).



Sample Usage:


DECLARE @Json nvarchar(MAX)

SET @Json = '{
  "firstName": "John",
  "lastName": "Smith",
  "age": 25,
  "address": {
    "streetAddress": "21 2nd Street",
    "city": "New York"
  },
  "phoneNumbers": [
    {
      "type": "home",
      "number": "212 555-1234"
    },
    {
      "type": "office",
      "number": "646 555-4567"
    },
    {
      "type": "mobile",
      "number": "123 456-7890"
    }
  ]
}'

SELECT *
FROM SQLHTTP.net.Json_To_OrdinalTable(@Json)

DECLARE @Json nvarchar(MAX)

SET @Json = '{

"firstName": "John",

"lastName": "Smith",

"age": 25,

"address": {

"streetAddress": "21 2nd Street",

"city": "New York"

"phoneNumbers": [

{

"type": "home",

"number": "212 555-1234"

{

"type": "office",

"number": "646 555-4567"

{

"type": "mobile",

"number": "123 456-7890"

}

]

SELECT *

FROM SQLHTTP.net.Json_To_OrdinalTable(@Json)

RowID ParentRowID NodePath Col4 Col5 Col6 Col7 Col8 Col9 Col10 ... Col1024

------- ------------- ---------------------------------- ----------- ---------------- ------ ------- -------- ---------------- -------- ----------

1 0 NoName NULL NULL 25 John Smith NULL NULL NULL

2 1 NoName/address{1} New York 21 2nd Street 25 John Smith NULL NULL NULL

8 1 NoName/phoneNumbers{1} NULL NULL 25 John Smith NULL NULL NULL

9 8 NoName/phoneNumbers{1}/NoName{1} NULL NULL 25 John Smith 212 555-1234 home NULL

12 8 NoName/phoneNumbers{1}/NoName{2} NULL NULL 25 John Smith 646 555-4567 office NULL

15 8 NoName/phoneNumbers{1}/NoName{3} NULL NULL 25 John Smith 123 456-7890 mobile NULL



Json_To_OrdinalTable

Featured API Wrapper Code / SQL Server