Parse JSON in TSQL

Question

Is it possible to parse JSON in TSQL?

I don't mean to create a JSON string; rather, I want to parse a JSON string passed in as a parameter.

Solution 1

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?

Solution 2

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:

Solution 3

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

Solution 4

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.

Solution 5

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;

View more solutions

Comments

[Phil Factor]

JSON is a pretty simple protocol so it really doesn't require a huge amount of masochism. Once you have it, you can use the one routine for all your JSON. Anyway, i've done it for you here simple-talk.com/sql/t-sql-programming/…

[ZygD]

Phil Factor: I've been reading your articles for many years. If you hadn't have written this article today I'd probably have quoted it 6 months ago when I answered...

[Gavin]

This is some great functionality but does have some limitations e.g. stripping the "-" from negative numbers.

[isapir]

very cool! you have one typo in the script: IF OBJECT_ID (N'dbo.parseJSON') IS NOT NULL DROP FUNCTION dbo.JSONEscaped GO -- should test for dbo.JSONEscaped in the IF test.

[cracker]

@phil dbo.parseJSON is working very slow in case of large data. so can we reduce the time of that by using any other methods within that?

[janv8000]

There's already an answer by the original author of the article: stackoverflow.com/a/4187412/389424

[Eralper]

Probably SQL Server 2016 CTP 3 will have the JSON to SQL Server support with OpenJSON syntax : kodyaz.com/t-sql/sql-server-2016-openjson-error.aspx

[Jovan MSFT]

There is a built-in support for parsing JSON text in new SQL Server 2016.

[egerardus]

+1 thanks works great, better than PhilFactor version. I had to dumb it down slightly for SQL Server 2008 though (no iif function or OFFSET)

[codeaf]

Here is a very helpful article from the Simple Talk website that outlines how to take a JSon string and output it into tables and columns that can be queried. This is for SQL Server 2016: https://www.simple-talk.com/sql/learn-sql-server/json-suppor‌​t-in-sql-server-2016‌​/

[Free Consulting]

I'm curious, what do you think about newly added native JSON support of SQL Server 2016?

[Nikoline Hejbøl]

This is awesome, but is there a way to make it not strip the "-" from negative numbers? I can't quite figure out where or why that happens...

[Fiach Reid]

Here is a code example on how to parse JSON using the CLR appoach: blog.dotnetframework.org/2016/12/06/…

[iamdave]

@NikolineHejbøl There is a fix for the negative number stripping in the comments: simple-talk.com/sql/t-sql-programming/…

[Baz Guvenkaya]

To make the json as variable instead of using json strings: declare @config varchar(1000) select @config = config from mytable (nolock) where id = 1 exec ('Select * from parseJSON(''' + @config + ''')')

[Jigar]

We are facing issue when StringValue is more than 4K size. Anyone has encounter such issue. We have comment field where we have NVarchar(Max) size. Does anyone has face this issue? What is solution for the same?

[kgzdev]

In database with Turkish_CI_AS Collation: parseJSON function query should replace variable names from parent_id to parent_ID and from string_id to string_ID to execute. Great work, thank you Phil!

[MGot90]

-1 For copying and pasting someone else answer without any explanation or links. This solution comes from RedGate written Nov 2010. red-gate.com/simple-talk/sql/t-sql-programming/… For those who are running SQL Server 2016+ don't use this because SQL Server introduced a native solution.

[MGot90]

docs.microsoft.com/en-us/sql/relational-databases/json/…

[Doug Coats]

Dude this is seriouosly frigggin awesome!

[André Voltolini]

Thanks for sharing.