How to use JSON with SQL Server?

Since SQL Server 2016, Miscrosoft adds features in T-SQL language to play with JSON.
See this article

Sample 1 : convert JSON to SQL Result query

DECLARE @jsonVariable NVARCHAR(MAX)

SET @jsonVariable = N'[  
        {  
          "Variable": {                          
			"Name":"SO43659",
			"Description":"Front Pump",  
			"Domain":"Water"
          },            
          "Info": {  
			"TS":"2011-05-31T00:00:00",  
            "Value":2024.9943,  
            "Quality":192  
          }  
        },  
        {  
          "Variable": {                          
			"Name":"SO43661",
			"Description":"Back Pump",  
			"Domain":"Water"
          },            
          "Info": {  
			"TS":"2011-06-01T00:00:00",  
            "Value":5014.7340,  
            "Quality":192  
          }
       }  
  ]'

SELECT *  
FROM OPENJSON (@jsonVariable)  
WITH 
(  
	[Chrono]	datetime	N'$.Info.TS',   
        [Name]		varchar(255)	N'$.Variable.Name',   
	[Description]	varchar(255)	N'$.Variable.Description', 
	[Domain]	varchar(100)	N'$.Variable.Domain', 
        [Value]		float		N'$.Info.Value',  
	[Quality]	smallint	N'$.Info.Quality'              
)    

Result:

Chrono	                Name	Description	Domain	Value	        Quality
2011-05-31 00:00:00.000	SO43659	Front Pump	Water	2024,9943	192
2011-06-01 00:00:00.000	SO43661	Back Pump	Water	5014,734	192

Sample 2 : extract table content to JSON

SELECT TOP (1000) * FROM [dbo].[TRENDTABLE] FOR JSON PATH

Result:

{"Chrono":130961232000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":0.000000000000000e+000,"Quality":192,"TS":"2016-01-01T13:00:00"},{"Chrono":130962096000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":8.414700000000001e-001,"Quality":192,"TS":"2016-01-02T13:00:00"},{"Chrono":130962960000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":9.093000000000000e-001,"Quality":192,"TS":"2016-01-03T13:00:00"},{"Chrono":130963824000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":1.411200000000000e-001,"Quality":192,"TS":"2016-01-04T13:00:00"},{"Chrono":130964688000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":7.568000000000000e-001,"Quality":192,"TS":"2016-01-05T13:00:00"},{"Chrono":130965552000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":9.589200000000000e-001,"Quality":192,"TS":"2016-01-06T13:00:00"},{"Chrono":130966416000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":2.794200000000000e-001,"Quality":192,"TS":"2016-01-07T13:00:00"},{"Chrono":130967280000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":6.569900000000000e-001,"Quality":192,"TS":"2016-01-08T13:00:00"},{"Chrono":130968144000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":9.893600000000000e-001,"Quality":192,"TS":"2016-01-09T13:00:00"},{"Chrono":130969008000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":4.121200000000000e-001,"Quality":192,"TS":"2016-01-10T13:00:00"},{"Chrono":130969872000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":5.440200000000000e-001,"Quality":192,"TS":"2016-01-11T13:00:00"},{"Chrono":130970736000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":9.999900000000001e-001,"Quality":192,"TS":"2016-01-12T13:00:00"},{"Chrono":130971600000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":5.365700000000000e-001,"Quality":192,"TS":"2016-01-13T13:00:00"},{"Chrono":130972464000000000,"Name":"DATA_ANALYSIS.1_DAY.TEMP","Value":4.201700000000000e-001,"Quality":192,"TS":"2016-01-14T13:00:00"}...

NB: Remember this feature to parse JSON quickly when you use DbConnect from SCADA Basic. B)