Using JSON in TSQL

-- Tests whether a string contains valid JSON.

declare @myjson nvarchar(max) = N'
[
    {
     "Product code":13496967,
     "Product":["one","aaa","two","bbb"],
     "Unit Cost":10.33,
     "Per Order":8
    },
    {
     "Product code":13496968,
     "Product":["three","ccc","four","ddd"],
     "Unit Cost":8.66,
     "Per Order":8
    }
]';

select isjson(@myjson);

-- Extracts a scalar value from a JSON string.

select JSON_VALUE(@myjson,'$[0]."Product code"') AS "Product code";
select JSON_VALUE(@myjson,'$[1]."Product code"') AS "Product code";

-- Extracts an object or an array from a JSON string.

select JSON_QUERY(@myjson,'$[0].Product') AS Product;
select JSON_QUERY(@myjson,'$[1].Product') AS Product;

-- Updates the value of a property in a JSON string and returns the updated JSON string.

select @myjson;
SET @myjson=JSON_MODIFY(@myjson,'$[0].Product[1]','zzz');
SET @myjson=JSON_MODIFY(@myjson,'$[1].Product[1]','yyy');
select @myjson;

-- Convert JSON collections to a rowset

select * from OPENJSON(@myjson);
SELECT [key], value FROM OPENJSON(@myjson,'$[0].Product');
SELECT [key], value FROM OPENJSON(@myjson,'$[1].Product');
SELECT *  
FROM OPENJSON(@myjson)  
  WITH ("Product code" bigint 'strict $."Product code"', 
        "Unit Cost" decimal(10,2) '$."Unit Cost"',
        "Per Order" tinyint '$."Per Order"');
SELECT *
FROM OPENJSON ( @myjson )  
WITH (   
              Number   bigint   '$."Product code"',  
              Cost     decimal(10,2)       '$."Unit Cost"',  
              per_order tinyint   '$."Per Order"',   
              [Product]  nvarchar(MAX)  AS JSON  
 );

 -- Convert a JSON array to a temporary table

DECLARE @pSearchOptions NVARCHAR(4000) = N'[8,10]'
SELECT *
FROM products
INNER JOIN OPENJSON(@pSearchOptions) AS productTypes
 ON products.[Per Order] = productTypes.value;

-- Convert SQL Server data to JSON or export JSON

select * from [dbo].[Products] as s where s.[Product code] = 13496967 FOR JSON PATH;
select * from [dbo].[Products] as s where s.[Product code] = 13496967 FOR JSON AUTO;

-- Index JSON properties by using computed columns

ALTER TABLE Sales.SalesOrderHeader ADD vCustomerName AS JSON_VALUE(Info,'$.Customer.Name');

CREATE INDEX idx_soh_json_CustomerName ON Sales.SalesOrderHeader(vCustomerName);