SQL Server JSON parsing method

A few days ago found that SQL Server 2016 supports the JSON project needs so the installation of a lot of convenience, write a small note to facilitate future viewing, but also hope that the great God to learn together.

There are many SQL Server 2016 installation diagrams on the Web, so let's pay attention to installing the version.

--1 with Root Key
SELECT * from Sys_menu for JSON AUTO, ROOT (' Result ')

--2 as
Select ID,
Name,
Age as [Entity.age],
Sex as [Entity.sex]
From Student for JSON path
--3 output of SQL with NULL columns

SELECT * from Sys_menu for JSON AUTO, ROOT (' Susu '), include_null_values

--4 parsing JSON--openjson (converting a JSON file to a normal data table)
DECLARE @JSalestOrderDetails NVarChar (+) = ' {' Ordersarray ': [
{"Number": 1, "Date": "8/10/2012", "Customer": "Adventure Works", "Quantity": 1200},
{"Number": 4, "Date": "5/11/2012", "Customer": "Adventure Works", "Quantity": 100},
{"Number": 6, "Date": "1/3/2012", "Customer": "Adventure Works", "Quantity": 250},
{"Number": 8, "Date": "12/7/2012", "Customer": "Adventure Works", "Quantity": 2200}
]}‘;

SELECT number, Customer, Date, Quantity
From Openjson (@JSalestOrderDetails, ' $. Ordersarray ')
With (
Number varchar (200),
Date datetime,
Customer varchar (200),
Quantity int
) as Ordersarray

--5 Isjson (determines if it is in JSON format)
Select Isjson (' {"AAA": 1} ')

--6 Json_value (parse JSON file and extract values): Remove the JSON object property as Name column
SELECT Json_value (' {' Order ': {' Type ': ' C ', ' Name ': ' Zhang San '} '} ', ' $. Order.name ')

SQL Server JSON parsing method