JSON Support for SQL Server

Documentation › Query Syntax

Query Syntax

Simple query syntax used in all JSON Select functions.

JSON Select query syntax is a straightforward syntax that is similar to accessing properties and arrays in JavaScript, C#, and many other programming languages.

Properties

Access properties simply using the name of the property.

Properties Example

declare @properties nvarchar(max), @property1Query nvarchar(max), @property3Query nvarchar(max) set @properties = '{ "Property1": "Value1", "Property2": { "Property3": "Value3" } }' set @property1Query = 'Property1' set @property3Query = 'Property2.Property3' set @properties = '{ "Property1": "Value1", "Property2": { "Property3": "Value3" } }' select dbo.JsonNVarCharMax(@properties, @property1Query) as [Property 1], dbo.JsonNVarCharMax(@properties, @property3Query) as [Property 3]

Results

Property 1 Property 3
Value1 Value3

Arrays

Access array members using square brackets and integer indexes starting from 0.

Arrays Example

declare @arrays nvarchar(max), @myArrayQuery nvarchar(max), @yourArrayQuery nvarchar(max) set @arrays = '{ "MyArray": [ 152, 253 ], "YourProperty": { "YourArray": [ "Value 0", "Value 1" ] } }' set @myArrayQuery = 'MyArray[0]' set @yourArrayQuery = 'YourProperty.YourArray[1]' select dbo.JsonInt(@arrays, @myArrayQuery) as [My Array Index 0], dbo.JsonNVarCharMax(@arrays, @yourArrayQuery) as [Your Array index 1]

Results

My Array Index 0 Your Array Index 1
152 Value 1

Query Flags

There are three flags that can be added to a query to modify the behaviour of the JSON Select query engine. To use query flags put the desired flag(s) followed by a pipe character (|) in front of the query.

i - Ignore Case Flag

The ignore case flag will cause your query to be case-insensitive.

x - Swallow Exceptions Flag

The swallow exceptions flag will catch any exceptions thrown by the JSON parser or query engine and return a null value. This is useful if your JSON is coming from a source that might not always send valid JSON. Note that this will not catch exceptions thrown by the JSON parser when using the JSON data type, as the parsing is done before any query is passed in.

? - Allow Undefined Flag

The allow undefined flag is used to allow for queries that may access undefined (i.e. non-existent) properties. Without the allow undefined flag JSON Select will throw an excetpion as soon as the query engine finds that part of a query does not exist within the JSON. When the allow undefined flag is used JSON Select will return null when it determines that any part of the query does not exist in the JSON.

Query Flags Example

declare @json nvarchar(max), @erroringQuery nvarchar(max), @ignoreCaseQuery nvarchar(max), @allowUndefinedQuery nvarchar(max), @badJson nvarchar(max), @swallowExceptionsQuery nvarchar(max) set @json = '{ "Property1": { "Property2": "A Value" } }' set @erroringQuery = 'PROPERTY1.property2' set @ignoreCaseQuery = 'i|PROPERTY1.property2' select dbo.JsonNVarCharMax(@json, @erroringQuery) as [Property 2] --Results: -- ERROR!! select dbo.JsonNVarCharMax(@json, @ignoreCaseQuery) as [Property 2]

Results

Property 2
A Value
set @json = '{ "Property1": { "Property2": "A Value" } }' set @erroringQuery = 'Property1.Property3' set @allowUndefinedQuery = '?|Property1.Property3' select dbo.JsonNVarCharMax(@json, @erroringQuery) as [Property 3] --Results: -- ERROR!! select dbo.JsonNVarCharMax(@json, @allowUndefinedQuery) as [Property 3]

Results

Property 3
NULL
set @badJson = '{ "Property1":: { "Property2": "A Value" } }' -- Oops! Two colons in a row, that's bad syntax! set @erroringQuery = 'Property1.Property2' set @swallowExceptionsQuery = 'x|Property1.Property2' select dbo.JsonNVarCharMax(@badJson, @erroringQuery) as [Property 2] --Results: -- ERROR!! select dbo.JsonNVarCharMax(@badJson, @swallowExceptionsQuery) as [Property 2]

Results

Property 2
NULL

Meta-Properties

There are six meta-properties that can be used to find out information about the data in your JSON. Meta-properties all begin with the @ symbol. If you have a property name in your JSON that begins with an @ symbol, you need to prefix it with an additional @ symbol to avoid having it treated as a meta-property by the query engine. Meta-property names are case-insensitive.

@length

The @length property returns the length of an array

@first

The @first property returns the first item in an array

@last

The @last property returns the last item in an array

@propertyCount

The @propertyCount property returns the number of properties in an object.

@propertyNames

The @propertyNames property returns an array of the property names in an object which can be accessed via indices or using the @first and @last meta-properties.

@propertyValues

The @propertyValues property returns an array of property values in an object which can be accessed via indices or using the @first and @last meta-properties.

Meta-Properties Example

declare @json nvarchar(max) set @json = '{ "Property1": { "Name 1": "Value 1", "Name 2": 2, "Name 3": "Value 3" }, "Property2": "Another Value", "Property3": "Yet another Value", "@Property4": "Value of Property 4", "Array": [ 9, 8, 7, 6 ] }' select dbo.JsonInt(@json, 'Array.@length') as [Array length], dbo.JsonInt(@json, 'Array.@first') as [Array first], dbo.JsonInt(@json, 'Array.@last') as [Array last] select dbo.JsonNVarCharMax(@json, 'Property1.@propertyCount') as [Property1 Property Count], dbo.JsonNVarCharMax(@json, 'Property1.@propertyNames.@first') as [Property1 First Name], dbo.JsonNVarCharMax(@json, 'Property1.@propertyValues[0]') as [Property1 Value at index 0], dbo.JsonNVarCharMax(@json, '@@Property4') as [@Property4]

Results

Array length Array first Array last
4 9 6
Property1 Property Count Property1 First Name Property1 Value at index 0 @Property4
3 Name 1 Value 1 Value of Property 4

A few more examples

Example

declare @bob json set @bob = '{ "Name": "Bob", "Friends": [ { "Name": "Jane", "Friends": [ "Tanya", "Jimmy", "Elizabeth" ] }, { "Name": "Chloe", "Friends": [ "James", "Monty", "Charles" ] } ] }' select @bob.QueryNVarCharMax('Name') as [Name], @bob.QueryNVarCharMax('Friends[0].Name') as [Friend 0 Name], @bob.QueryNVarCharMax('Friends[1].Friends[2]') as [Friend 1's Friend 2]

Results

Name Friend 0 Name Friend 1's Friend 2
Bob Jane Charles

Example

declare @friends nvarchar(max) set @friends = '[ { "Name": "Jane", "Friends": [ "Tanya", "Jimmy", "Elizabeth" ] }, { "Name": "Chloe", "Friends": [ "James", "Monty", "Charles" ] } ]' select dbo.JsonNVarCharMax(@friends, '[0].Name') as [Friend 0 Name], dbo.JsonNVarCharMax(@friends, '[1].Name') as [Friend 1 Name], dbo.JsonNVarCharMax(@friends, '[1].Friends[1]') as [Friend 1's Friend 1]

Results

Friend 0 Name Friend 1 Name Friend 1's Friend 1
Jane Chloe Monty