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

User · Answer

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:

User · Answer

Now there is a Native support in SQL Server  CTP3  for import  export  query and validate JSON inside T-SQL Refer to https   msdn microsoft com en-us library dn921897 aspx

User · Answer

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

User · Answer

I have seen a pretty neat article about this    so if you like this   CREATE PROC  dbo   spUpdateMarks       inputJSON VARCHAR MAX   --     ID   1   C   60   CPP   60   CS   60     AS BEGIN     -- Temp table to hold the parsed data     DECLARE  TempTableVariable TABLE          element id INT          sequenceNo INT          parent ID INT           Object ID  INT           NAME  NVARCHAR 2000           StringValue NVARCHAR MAX           ValueType NVARCHAR 10            -- Parse JSON string into a temp table     INSERT INTO  TempTableVariable     SELECT   FROM parseJSON  inputJSON  END   Try to look here   https   www simple-talk com sql t-sql-programming consuming-json-strings-in-sql-server   There is a complete ASP Net project about this here  http   www codeproject com Articles 788208 Update-Multiple-Rows-of-GridView-using-JSON-in-ASP

User · Answer

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

User · Answer

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

User · Answer

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    Reverse process  from hierarchy to JSON Querying functions  to fetch particular values from a JSON object   Please feel free to use and have fun with it  http   www codeproject com Articles 1000953 JSON-for-Sql-Server-Part

User · Answer

CREATE FUNCTION dbo parseJSON   JSON NVARCHAR MAX   RETURNS  hierarchy TABLE        element id INT IDENTITY 1  1  NOT NULL     internal surrogate primary key gives the order of parsing and the list order       sequenceNo  int  NULL     the place in the sequence for the element       parent ID INT    if the element has a parent then it is in this column  The document is the ultimate parent  so you can get the structure from recursing from the document       Object ID INT    each list or object has an object id  This ties all elements to a parent  Lists are treated as objects here       NAME NVARCHAR 2000     the name of the object       StringValue NVARCHAR MAX  NOT NULL   the string representation of the value of the element        ValueType VARCHAR 10  NOT null    the declared type of the value represented as a string in StringValue       AS BEGIN   DECLARE      FirstObject INT  --the index of the first open bracket found in the JSON string      OpenDelimiter INT --the index of the next open bracket found in the JSON string      NextOpenDelimiter INT --the index of subsequent open bracket found in the JSON string      NextCloseDelimiter INT --the index of subsequent close bracket found in the JSON string      Type NVARCHAR 10  --whether it denotes an object or an array      NextCloseDelimiterChar CHAR 1  --either a     or a          Contents NVARCHAR MAX   --the unparsed contents of the bracketed expression      Start INT  --index of the start of the token that you are parsing      end INT --index of the end of the token that you are parsing      param INT --the parameter at the end of the next Object Array token      EndOfName INT --the index of the start of the parameter at end of Object Array token      token NVARCHAR 200  --either a string or object      value NVARCHAR MAX   -- the value as a string      SequenceNo int  -- the sequence number within a list      name NVARCHAR 200   --the name as a string      parent ID INT --the next parent ID to allocate      lenJSON INT --the current length of the JSON String      characters NCHAR 36  --used to convert hex to decimal      result BIGINT --the value of the hex symbol being parsed      index SMALLINT --used for parsing the hex value      Escape INT --the index of the next escape character     DECLARE  Strings TABLE    in this temporary table we keep all strings  even the names of the elements  since they are  escaped  in a different way  and may contain  unescaped  brackets denoting objects or lists  These are replaced in the JSON string by tokens representing the string               String ID INT IDENTITY 1  1        StringValue NVARCHAR MAX          SELECT--initialise the characters to convert hex to ascii      characters  0123456789abcdefghijklmnopqrstuvwxyz        SequenceNo 0  --set the sequence no  to something sensible       firstly we process all strings  This is done because     and   aren t escaped in strings  which complicates an iterative parse          parent ID 0    WHILE 1 1 --forever until there is nothing more to do     BEGIN       SELECT          start PATINDEX     a-zA-Z         json collate SQL Latin1 General CP850 Bin  --next delimited string       IF  start 0 BREAK --no more so drop through the WHILE loop       IF SUBSTRING  json   start 1  1              BEGIN --Delimited Name           SET  start  Start 1            SET  end PATINDEX              RIGHT  json  LEN  json     - start  collate SQL Latin1 General CP850 Bin           END       IF  end 0 --no end delimiter to last string         BREAK --no more       SELECT  token SUBSTRING  json   start 1   end-1        --now put in the escaped control characters       SELECT  token REPLACE  token  FROMString  TOString        FROM          SELECT                AS FromString      AS ToString          UNION ALL SELECT                    UNION ALL SELECT                    UNION ALL SELECT   b   CHAR 08           UNION ALL SELECT   f   CHAR 12           UNION ALL SELECT   n   CHAR 10           UNION ALL SELECT   r   CHAR 13           UNION ALL SELECT   t   CHAR 09            substitutions       SELECT  result 0   escape 1   --Begin to take out any hex escape codes       WHILE  escape gt 0         BEGIN           SELECT  index 0            --find the next hex escape sequence            escape PATINDEX    x 0-9a-f  0-9a-f  0-9a-f  0-9a-f      token collate SQL Latin1 General CP850 Bin            IF  escape gt 0 --if there is one             BEGIN               WHILE  index lt 4 --there are always four digits to a  x sequence                   BEGIN                   SELECT --determine its value                      result  result POWER 16   index                        CHARINDEX SUBSTRING  token   escape 2 3- index  1                                    characters -1    index  index 1                    END                 -- and replace the hex sequence by its unicode value               SELECT  token STUFF  token   escape  6  NCHAR  result               END         END       --now store the string away       INSERT INTO  Strings  StringValue  SELECT  token       -- and replace the string with a token       SELECT  JSON STUFF  json   start   end 1                        string  CONVERT NVARCHAR 5     identity       END   -- all strings are now removed  Now we find the first leaf     WHILE 1 1  --forever until there is nothing more to do   BEGIN    SELECT  parent ID  parent ID 1   --find the first object or list by looking for the open bracket   SELECT  FirstObject PATINDEX             json collate SQL Latin1 General CP850 Bin --object or array   IF  FirstObject   0 BREAK   IF  SUBSTRING  json   FirstObject  1           SELECT  NextCloseDelimiterChar       type  object    ELSE     SELECT  NextCloseDelimiterChar       type  array    SELECT  OpenDelimiter  firstObject    WHILE 1 1 --find the innermost object or list        BEGIN       SELECT          lenJSON LEN  JSON     -1   --find the matching close-delimiter proceeding after the open-delimiter       SELECT          NextCloseDelimiter CHARINDEX  NextCloseDelimiterChar   json                                         OpenDelimiter 1    --is there an intervening open-delimiter of either type       SELECT  NextOpenDelimiter PATINDEX                         RIGHT  json   lenJSON- OpenDelimiter collate SQL Latin1 General CP850 Bin --object       IF  NextOpenDelimiter 0         BREAK       SELECT  NextOpenDelimiter  NextOpenDelimiter  OpenDelimiter       IF  NextCloseDelimiter lt  NextOpenDelimiter         BREAK       IF SUBSTRING  json   NextOpenDelimiter  1              SELECT  NextCloseDelimiterChar       type  object        ELSE         SELECT  NextCloseDelimiterChar       type  array        SELECT  OpenDelimiter  NextOpenDelimiter     END   ---and parse out the list or name value pairs   SELECT      contents SUBSTRING  json   OpenDelimiter 1                           NextCloseDelimiter- OpenDelimiter-1    SELECT      JSON STUFF  json   OpenDelimiter                   NextCloseDelimiter- OpenDelimiter 1                       type CONVERT NVARCHAR 5    parent ID     WHILE  PATINDEX    A-Za-z0-9   e      contents collate SQL Latin1 General CP850 Bin   lt  gt 0     BEGIN       IF  Type  Object  --it will be a 0-n list containing a string followed by a string  number boolean  or null         BEGIN           SELECT              SequenceNo 0  end CHARINDEX           contents --if there is anything  it will be a string-based name            SELECT   start PATINDEX     A-Za-z              contents collate SQL Latin1 General CP850 Bin --AAAAAAAA           SELECT  token SUBSTRING      contents   start 1   End- Start-1                endofname PATINDEX    0-9      token collate SQL Latin1 General CP850 Bin                param RIGHT  token  LEN  token - endofname 1            SELECT              token LEFT  token   endofname-1                Contents RIGHT      contents  LEN      contents     - end-1            SELECT   name stringvalue FROM  strings             WHERE string id  param --fetch the name         END       ELSE         SELECT  Name null  SequenceNo  SequenceNo 1       SELECT          end CHARINDEX       contents -- a string-token  object-token  list-token  number boolean  or null       IF  end 0         SELECT   end PATINDEX    A-Za-z0-9   e   A-Za-z0-9   e      Contents     collate SQL Latin1 General CP850 Bin             1        SELECT          start PATINDEX     A-Za-z0-9   e  A-Za-z0-9   e          contents collate SQL Latin1 General CP850 Bin        --select  start  end  LEN  contents        contents        SELECT          Value RTRIM SUBSTRING  contents   start   End- Start             Contents RIGHT  contents      LEN  contents     - end        IF SUBSTRING  value  1  7    object          INSERT INTO  hierarchy            NAME  SequenceNo  parent ID  StringValue  Object ID  ValueType            SELECT  name   SequenceNo   parent ID  SUBSTRING  value  8  5               SUBSTRING  value  8  5    object        ELSE         IF SUBSTRING  value  1  6    array            INSERT INTO  hierarchy              NAME  SequenceNo  parent ID  StringValue  Object ID  ValueType              SELECT  name   SequenceNo   parent ID  SUBSTRING  value  7  5                 SUBSTRING  value  7  5    array          ELSE           IF SUBSTRING  value  1  7    string              INSERT INTO  hierarchy                NAME  SequenceNo  parent ID  StringValue  ValueType                SELECT  name   SequenceNo   parent ID  stringvalue   string                FROM  strings               WHERE string id SUBSTRING  value  8  5            ELSE             IF  value IN   true    false                 INSERT INTO  hierarchy                  NAME  SequenceNo  parent ID  StringValue  ValueType                  SELECT  name   SequenceNo   parent ID   value   boolean              ELSE               IF  value  null                  INSERT INTO  hierarchy                    NAME  SequenceNo  parent ID  StringValue  ValueType                    SELECT  name   SequenceNo   parent ID   value   null                ELSE                 IF PATINDEX     0-9      value collate SQL Latin1 General CP850 Bin  gt 0                   INSERT INTO  hierarchy                      NAME  SequenceNo  parent ID  StringValue  ValueType                      SELECT  name   SequenceNo   parent ID   value   real                  ELSE                   INSERT INTO  hierarchy                      NAME  SequenceNo  parent ID  StringValue  ValueType                      SELECT  name   SequenceNo   parent ID   value   int        if  Contents     Select  SequenceNo 0     END   END INSERT INTO  hierarchy  NAME  SequenceNo  parent ID  StringValue  Object ID  ValueType    SELECT  -  1  NULL       parent id-1   type --    RETURN END GO   ---Pase JSON  Declare  pars varchar MAX         shapes     type   polygon   geofenceName      geofenceDescription       geofenceCategory   1   color    1E90FF   paths     path      lat   26 096254906968525   lon   65 709228515625      lat   28 38173504322308   lon   66 741943359375      lat   26 765230565697482   lon   68 983154296875      lat   26 254009699865737   lon   68 609619140625      lat   25 997549919572112   lon   68 104248046875      lat   26 843677401113002   lon   67 115478515625      lat   25 363882272740255   lon   65 819091796875          Select   from parseJSON  pars  AS MyResult

User · Answer

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

[sql-server] Parse JSON in TSQL

Examples related to sql-server

Examples related to tsql

Examples related to json