JSON Functions in SQL Server 2016

JSON Functions in SQL Server 2016.sql

 --ISJSON() - To verify that the text has valid JSON data
DECLARE @json nvarchar(max) 
  SET @json =N'
{
  "Person": [
    {
      "FirstName": "Gustavo",
      "LastName": "Achong",
      "AccountNumber": "AW00029484",
      "ModifiedDate": "2014-09-12T11:15:07.263"
    }
  ]
}'

IF (ISJSON(@json) = 1)
PRINT 'It is a Valid JSON' --Valid JSON
ELSE
PRINT 'It is an Invalid JSON string'

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

DECLARE @json nvarchar(max) 
  SET @json =N'
{
  "Person": [
    {
      "FirstName": "Gustavo",
      "LastName": "Achong",
      "AccountNumber": "AW00029484",
      "ModifiedDate" "2014-09-12T11:15:07.263"
    }
  ]
}'

IF (ISJSON(@json) = 1)
PRINT 'It is a Valid JSON'
ELSE
PRINT 'It is an Invalid JSON string' -- Invalid JSON

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

-- JSON_VALUE Demonstration to pull one scalar value from the JSON data
DECLARE @json nvarchar(max) 
  SET @json =N'
{
  "Person": [
    {
      "FirstName": "Gustavo",
      "LastName": "Harris",
      "AccountNumber": "AW00029484",
      "ModifiedDate": "2014-09-12T11:15:07.263"
    },
    {
      "FirstName": "Catherine",
      "LastName": "Abel",
      "AccountNumber": "AW00029485",
      "ModifiedDate": "2014-09-12T11:15:07.263"
    }
  ]
}'

-- Extract scalar value from a JSON text 
SELECT JSON_VALUE(@json, '$.Person[0].LastName');
SELECT JSON_VALUE(@json, '$.Person[1].AccountNumber');

-- Returns NULL if the path does not exist
SELECT JSON_VALUE(@json, '$.Person[1].Age');

-- lax option is by default and returns NULL if there is a problem
SELECT JSON_VALUE(@json, 'lax $.Person[1].Age');

-- strict option raises an error if there is a problem
SELECT JSON_VALUE(@json, 'strict $.Person[1].Age');

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


-- JSON_QUERY Demonstration to extract an object or array from the JSON data
DECLARE @json nvarchar(max) 
  SET @json =N'
{
  "Person": [
    {
      "Name": {
        "FirstName": "Catherine",
        "LastName": "Abel"
      },
      "Address": {
        "State": "VA",
        "Zip": "24153"
      }
    }
  ]
}'


IF (ISJSON(@json) = 1)
PRINT 'It is a Valid JSON' --Valid JSON
ELSE
PRINT 'It is an Invalid JSON string' 


SELECT JSON_QUERY(@json, '$.Person');

-- Extract object/array value from a JSON text 
SELECT JSON_QUERY(@json, '$.Person[0].Address');

-- Returns NULL if the path does not exist
SELECT JSON_QUERY(@json, '$.Person[0].Age');

-- lax option is by default and returns NULL if there is a problem
SELECT JSON_QUERY(@json, 'lax $.Person[0].Age');

-- strict option raises an error if there is a problem
SELECT JSON_QUERY(@json, 'strict $.Person[0].Age');

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

-- JSON_MODIFY Demonstration to update the value of a property in JSON string
DECLARE @json nvarchar(max) 
  SET @json =N'
{
  "Person": [
    {
      "Name": {
        "FirstName": "Catherine",
        "LastName": "Abel"
      },
      "Address": {
        "State": "VA",
        "Zip": "24153"
      }
    }
  ]
}'

PRINT @json


IF (ISJSON(@json) = 1)
PRINT 'It is a Valid JSON' --Valid JSON
ELSE
PRINT 'It is an Invalid JSON string' 

PRINT JSON_MODIFY (@json, '$.Person[0].Address.Zip' , '35043');



PRINT @json




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