In my previous posts I talked about how to [export] a Table or Query data into JSON string format, and [read it back] from JSON string to Relational-table format, and with [nested elements] .

Here, in this post I’ll show how we can store JSON data in a normal table column, just like you store XML data.

XML data is stored in a column of XML datatype which also check the validity of the XML data to be stored. But to store JSON data there is no new datatype introduced, JSON can be stored in an NVARCHAR datatype column just like a plain text, and to validate it you can add a CHECK constraint on it.

IsJSON() function:can be used as a CHECK constraint on the columns that contain JSON string which will validate if the JSON string is in proper format or not.

As we will need AdvantureWorks2014 Sample Database in our example below, we need to upgrade its Compatibility from SQL 2014 to SQL 2016, i.e. from level 120 to 130, like:

USE [master]
GO
ALTER DATABASE [AdventureWorks2014] SET COMPATIBILITY_LEVEL = 130
GO You can download AdvantureWorks2014 sample Database from Microsoft [CodePlex site] .

–>Ok, now let’s create a new Table with OrderDetailsJSON column for storing JSON string with a CHECK constraint on it:

USE [AdventureWorks2014]
GO
CREATE TABLE CustomerOrder (
BusinessEntityID INT,
FirstName NVARCHAR(50),
MiddleName NVARCHAR(50),
LastName NVARCHAR(50),
EmailPromotion INT,
OrderDetailsJSON NVARCHAR(MAX) -- normal column with NVARCHAR datatype
CHECK ( IsJSON ( OrderDetailsJSON ) = 1 ) -- CHECK Constraint to validate JSON string
)

–>Let’s create a sample record-set with JSON data in OrderDetailsJSON column. We will use FOR JSON AUTO option to convert relational data to JSON string for our example, as shown below:

;WITH CTE_PersonContact AS (
SELECT
BusinessEntityID, FirstName, MiddleName, LastName, EmailPromotion,
OrderDetailsJSON =
( SELECT SalesOrderID, OrderDate, SubTotal, TaxAmt, TotalDue
FROM [AdventureWorks2014].[Sales].[SalesOrderHeader] S
WHERE S.CustomerID = P.BusinessEntityID
FOR JSON AUTO -- here
) -- our JSON column
FROM [Person].[Person] P
)
INSERT INTO CustomerOrder
SELECT
BusinessEntityID, FirstName, MiddleName, LastName, EmailPromotion,
OrderDetailsJSON
FROM CTE_PersonContact
WHERE OrderDetailsJSON IS NOT NULL
-- (9778 row(s) affected)

–>Check the above inserted records with the OrderDetailsJSON column containing data in JSON format:

SELECT * FROM CustomerOrder
-- (9778 row(s) affected)

–>Let’s Query back the JSON data from the OrderDetailsJSON column with other columns in relational form, by using OPENJSON() function. As for each Customer it can contain multiple orders we will get multiple rows for each Customer and multiple columns as per defined in the JSON string:

SELECT
C.BusinessEntityID, C.FirstName, C.MiddleName, C.LastName, C.EmailPromotion,
J.SalesOrderID, J.OrderDate, J.SubTotal, J.TaxAmt, J.TotalDue
FROM CustomerOrder C
CROSS APPLY OPENJSON (OrderDetailsJSON)
WITH (
SalesOrderID INT,
OrderDate DATETIME,
SubTotal MONEY,
TaxAmt MONEY,
TotalDue MONEY
) AS J
-- (17463 row(s) affected)

–>And if you want to get just one Order per Customer then you can use following Query, by using JSON_Value() function and by specifying the array key pointer/position to get the first value fro the array:

SELECT
C.BusinessEntityID, C.FirstName, C.MiddleName, C.LastName, C.EmailPromotion,
SalesOrderID = JSON_Value (OrderDetailsJSON, '$[0].SalesOrderID'),
OrderDate = JSON_Value (OrderDetailsJSON, '$[0].OrderDate'),
SubTotal = JSON_Value (OrderDetailsJSON, '$[0].SubTotal'),
TaxAmt = JSON_Value (OrderDetailsJSON, '$[0].TaxAmt'),
TotalDue = JSON_Value (OrderDetailsJSON, '$[0].TotalDue')
FROM CustomerOrder C
-- (9778 row(s) affected) We can also use JSON string for passing multiple values from a single parameter in an Stored Procedure to implement dynamic parameters functionality as a workaround, check the [blog post] for the same.

本文数据库（mssql）相关术语:熊片数据库 mssql数据库 oracle数据库 pubmed数据库 access数据库 万方数据库

分页：12

转载请注明
本文标题：Store JSON data in a table, OPENJSON and JSON_Value functions | SQL Server 2016 – Part 4
本站链接：
分享请点击：

1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责；
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性，不作出任何保证或承若；
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。

登录后可拥有收藏文章、关注作者等权限...

   注册     登录