I seem to have a huge masochistic streak in that I've written a JSON parser. It converts a JSON document into a SQL Adjacency list table, which is easy to use to update your data tables. Actually, I've done worse, in that I've done code to do the reverse process, which is to go from a hierarchy table to a JSON string
The article and code is here: Consuming Json strings in SQL server.
Select * from parseJSON('{ "Person": { "firstName": "John", "lastName": "Smith", "age": 25, "Address": { "streetAddress":"21 2nd Street", "city":"New York", "state":"NY", "postalCode":"10021" }, "PhoneNumbers": { "home":"212 555-1234", "fax":"646 555-4567" } } } ')
To get:
Update: As of SQL Server 2016 parsing JSON in TSQL is now possible.
Natively, there is no support. You'll have to use CLR. It is as simple as that, unless you have a huge masochistic streak and want to write a JSON parser in SQL
Normally, folk ask for JSON output from the DB and there are examples on the internet. But into a DB?
Finally SQL Server 2016 will add Native JSON support!!
Ref:
Additional capabilities in SQL Server 2016 include:
- Additional security enhancements for Row-level Security and Dynamic Data Masking to round out our security investments with Always
Encrypted.- Improvements to AlwaysOn for more robust availability and disaster recovery with multiple synchronous replicas and secondary load
balancing.- Native JSON support to offer better performance and support for your many types of your data.
- SQL Server Enterprise Information Management (EIM) tools and Analysis Services get an upgrade in performance, usability and scalability.
- Faster hybrid backups, high availability and disaster recovery scenarios to backup and restore your on-premises databases to Azure
and place your SQL Server AlwaysOn secondaries in Azure.
Announcment: http://blogs.technet.com/b/dataplatforminsider/archive/2015/05/04/sql-server-2016-public-preview-coming-this-summer.aspx
Features blog post: http://blogs.msdn.com/b/jocapc/archive/2015/05/16/json-support-in-sql-server-2016.aspx
SQL server 2016 supports json data
parsing using OPENJSON
. You can use OPENJSON
to map json data
to rows and columns.
Your json Data
[
{ "id" : 2,"name": "John"},
{ "id" : 5,"name": "John"}
]
Here is how you can handle json in sql
//@pJson is json data passed from code.
INSERT INTO YourTable (id, Name)
SELECT id, name
FROM OPENJSON(@pJson)
WITH (id int,
name nvarchar(max))
Here is a detailed article which covers this topic.
I do also have a huge masochistic streak as that I've written yet another JSON parser. This one uses a procedural approach. It uses a similat SQL hierarchy list table to store the parsed data. Also in the package are:
Please feel free to use and have fun with it
http://www.codeproject.com/Articles/1000953/JSON-for-Sql-Server-Part
I developed my own SQL Server 2016+ JSON parser a while ago. I use this in all my projects - very good performance. I hope it can help someone else too.
Full code of the function:
ALTER FUNCTION [dbo].[SmartParseJSON] (@json NVARCHAR(MAX))
RETURNS @Parsed TABLE (Parent NVARCHAR(MAX),Path NVARCHAR(MAX),Level INT,Param NVARCHAR(4000),Type NVARCHAR(255),Value NVARCHAR(MAX),GenericPath NVARCHAR(MAX))
AS
BEGIN
-- Author: Vitaly Borisov
-- Create date: 2018-03-23
;WITH crData AS (
SELECT CAST(NULL AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS [Parent]
,j.[Key] AS [Param],j.Value,j.Type
,j.[Key] AS [Path],0 AS [Level]
,j.[Key] AS [GenericPath]
FROM OPENJSON(@json) j
UNION ALL
SELECT CAST(d.Path AS NVARCHAR(4000)) COLLATE DATABASE_DEFAULT AS [Parent]
,j.[Key] AS [Param],j.Value,j.Type
,d.Path + CASE d.Type WHEN 5 THEN '.' WHEN 4 THEN '[' ELSE '' END + j.[Key] + CASE d.Type WHEN 4 THEN ']' ELSE '' END AS [Path]
,d.Level+1
,d.GenericPath + CASE d.Type WHEN 5 THEN '.' + j.[Key] ELSE '' END AS [GenericPath]
FROM crData d
CROSS APPLY OPENJSON(d.Value) j
WHERE ISJSON(d.Value) = 1
)
INSERT INTO @Parsed(Parent, Path, Level, Param, Type, Value, GenericPath)
SELECT d.Parent,d.Path,d.Level,d.Param
,CASE d.Type
WHEN 1 THEN CASE WHEN TRY_CONVERT(UNIQUEIDENTIFIER,d.Value) IS NOT NULL THEN 'UNIQUEIDENTIFIER' ELSE 'NVARCHAR(MAX)' END
WHEN 2 THEN 'INT'
WHEN 3 THEN 'BIT'
WHEN 4 THEN 'Array'
WHEN 5 THEN 'Object'
ELSE 'NVARCHAR(MAX)'
END AS [Type]
,CASE
WHEN d.Type = 3 AND d.Value = 'true' THEN '1'
WHEN d.Type = 3 AND d.Value = 'false' THEN '0'
ELSE d.Value
END AS [Value]
,d.GenericPath
FROM crData d
OPTION(MAXRECURSION 1000) /*Limit to 1000 levels deep*/
;
RETURN;
END
GO
Example of use:
DECLARE @json NVARCHAR(MAX) = '{"Objects":[{"SomeKeyID":1,"Value":3}],"SomeParam":"Lalala"}';
SELECT j.Parent, j.Path, j.Level, j.Param, j.Type, j.Value, j.GenericPath
FROM dbo.SmartParseJSON(@json) j;
Example of multilevel use:
DECLARE @json NVARCHAR(MAX) = '{"Objects":[{"SomeKeyID":1,"Value":3}],"SomeParam":"Lalala"}';
DROP TABLE IF EXISTS #ParsedData;
SELECT j.Parent, j.Path, j.Level, j.Param, j.Type, j.Value, j.GenericPath
INTO #ParsedData
FROM dbo.SmartParseJSON(@json) j;
SELECT COALESCE(p2.GenericPath,p.GenericPath) AS [GenericPath]
,COALESCE(p2.Param,p.Param) AS [Param]
,COALESCE(p2.Value,p.Value) AS [Value]
FROM #ParsedData p
LEFT JOIN #ParsedData p1 ON p1.Parent = p.Path AND p1.Level = 1
LEFT JOIN #ParsedData p2 ON p2.Parent = p1.Path AND p2.Level = 2
WHERE p.Level = 0
;
DROP TABLE IF EXISTS #ParsedData;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With