JSON Support for SQL Server

Documentation › JSON Data Type

JSON Data Type

Use within scripts or in a table to store a parsed JSON obect graph.

The JSON data type holds a parsed object representation of the JSON string it was created with. It has functions that are similar to the stand-alone functions, for example the dbo.JsonInt(data, query) function has the JSON data type equivalent QueryInt(query).

The JSON data type is suitable for use in scripts, stored procedures etc. where it allows you to parse JSON once and query it multiple times. It's use for persistent storage is not necessarily recommended, as depending on the size and complexity of your JSON, parsing using the stand-alone functions can be quicker than saving and retrieving the JSON data type's binary format.

Functions

JsonDataType QueryBigInt(query)
JsonDataType QueryBit(query)
JsonDataType QueryDateTime(query, format)
JsonDataType QueryDecimal(query)
JsonDataType QueryFloat(query)
JsonDataType QueryInt(query)
JsonDataType QueryNVarChar(query, length)
JsonDataType QueryNVarChar1(query)
JsonDataType QueryNVarChar450(query)
JsonDataType QueryNVarCharMax(query)
JsonDataType.ToString()

Example

declare @orderData json set @orderData = '{ "Name": "Bob Smith", "ItemCount": 12, "Total": 15.54, "Packed": true, "Shipped": false, "OrderDate": "2015-02-10 14:52:23", "Lines": [ { "Item": "Hat", "Quantity": 10, "UnitPrice": 1.11, "Total": 11.1 }, { "Item": "Scarf", "Quantity": 2, "UnitPrice": 2.22, "Total": 4.44 } ] }' select @orderData.QueryBigInt('Lines[0].Quantity') as [Line 0 Quantity], @orderData.QueryBigInt('Lines[1].Quantity') as [Line 1 Quantity], @orderData.QueryBigInt('ItemCount') as [Total Count]

Results

Line 0 Quantity Line 1 Quantity Total Count
10 2 12