JSON Support for SQL Server

Documentation › JsonNVarChar1

JsonNVarChar1(data, query) or JsonDataType QueryNVarChar1(query)

Query a string value stored within a JSON string. Output will be truncated to 1 character.

Example

JsonNVarChar1(data, query)

JsonDataType QueryNVarChar1(query)

declare @orderData nvarchar(1000) 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, "StatusCode": "A" }, { "Item": "Scarf", "Quantity": 2, "UnitPrice": 2.22, "Total": 4.44, "StatusCode": "X" } ] }' select dbo.JsonNVarChar1(@orderData, 'Lines[0].Item') as [Line 0 Item First Char], dbo.JsonNVarChar1(@orderData, 'Lines[0].StatusCode') as [Line 0 Status Code]

Results

Line 0 Item First Char Line 0 Status Code
H A

JsonDataType QueryNVarChar1(query)

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, "StatusCode": "A" }, { "Item": "Scarf", "Quantity": 2, "UnitPrice": 2.22, "Total": 4.44, "StatusCode": "X" } ] }' select @orderData.QueryNVarChar1('Lines[0].Item') as [Line 0 Item First Char], @orderData.QueryNVarChar1('Lines[0].StatusCode') as [Line 0 Status Code]

Results

Line 0 Item First Char Line 0 Status Code
H A