Querying JSON in SQL

Question

I'm trying to find a way to query JSON in SQL similarly to querying XML. Any ideas? I don't have any data saved yet, so I'm pretty much open, although I've read the best way to save JSON is by using a column that is varchar(max).

Thanks

Example:

Let's say the following JSON object is stored in a varchar(max) column, and I want to query the column pulling out all transaction IDs with the "success" flag = False.

{"TransactionID":"sample string 1","Success":true,
 "Response":"sample string 3","Values":"sample string 4"}

Answer 1

Moral of the story: Don't do this using SQL. CLR and a JSON wrapper are the way to go. But if you are truly curious you could do something like below. There are so many problems that could happen it would be difficult to list them all. A quick google on the topic will easily show why. If you can ensure perfectly perfectly perfectly formed JSON objects this may be something you could look into. Maybe. Don't use this.

/*
JSON to XML Parser TSQL
Compatibility: TEsted on SQL Server 2014
Assumptions:    -Perfectly formed JSON as declared below.
                -Charecters for formatting are ok but will be removed
*/

--Imagine @JSONDATA would be the parameter to your function
DECLARE @JSONDATA VARCHAR(MAX) = '{"transactions":[
                                        {"TransacitonID":"transaction1","Success":true,"Response":"sample string 1","Values":"sample string 1"},
                                        {"TransactionID":"transaction2","Success":false,"Response":"sample string 2","Values":"sample string 2"}
                                ]}'

DECLARE @ObjectArrayNameStart INT
DECLARE @ObjectArrayNameEnd INT
DECLARE @ObjectArray VARCHAR(MAX)
DECLARE @ObjectArrayName VARCHAR(MAX)
DECLARE @ObjectArrayBracketStart INT
DECLARE @ObjectArrayBracketEnd INT
DECLARE @ObjectArraySingleton VARCHAR(MAX)
DECLARE @ObjectXML XML



--Replace All CR and LF charecters and HT chars
SET @JSONDATA = REPLACE(REPLACE(REPLACE(@JSONDATA,CHAR(10),''),CHAR(13),''),CHAR(9),'') --LF

--Get the ObjectArrayName
SELECT @ObjectArrayNameStart = PATINDEX('%{["]transactions["]:[[]%',@JSONDATA), @ObjectArrayNameEnd = PATINdEX('%[[]%',@JSONDATA)
SET @ObjectArrayName = SUBSTRING(@JSONDATA,@ObjectArrayNameStart,@ObjectArrayNameEnd)

--Get the ObjectArray name
SET @ObjectArray = REPLACE(@JSONDATA,@ObjectArrayName,'')

----Trim out the word of the object array and get the single word and singleton item
SET @ObjectArrayName = LEFT(@ObjectArrayName,@ObjectArrayNameEnd - 3)
SET @ObjectArrayName = RIGHT(@ObjectArrayName, LEN(@ObjectArrayName) - 2)
SET @ObjectArraySingleton = 'node'

--PREP THE JSON OBJECT DOWN TO INDIVIDUAL OBJECTS AND SET AS XML DOCUMENT
SET @ObjectArray = LTRIM(RTRIM(REPLACE(@ObjectArray,']}','')))
SET @ObjectArray = REPLACE(@ObjectArray,'},{','</' + @ObjectArraySingleton + '><' + @ObjectArraySingleton + '>')
SET @ObjectArray = REPLACE(@ObjectArray,'{','<' + @ObjectArraySingleton + '>')
SET @ObjectArray = REPLACE(@ObjectArray,'}','</' + @ObjectArraySingleton + '>')
SET @ObjectArray = '<root>' + @ObjectArray + '</root>'
SET @ObjectXML =  CAST(@ObjectArray AS XML)

--Query for line data
;WITH XMLObjectData AS (
    SELECT  Item.value('text()[1]','nvarchar(max)') AS ObjectLine
    FROM    @ObjectXML.nodes('/root/node') AS Items(Item)
), CommaSplit AS (
    SELECT  '<pair><key>' + REPLACE(X.ObjectLine,',','</value></pair><pair><key>') + '</value></pair>' AS ObjectLine
    FROM    XMLObjectData AS X
), ColonSplit AS (
    SELECT  REPLACE(X.ObjectLine,':','</key><value>') AS ObjectLine
    FROM    CommaSplit AS X
), QuoteReplace AS (
    SELECT  REPLACE(X.ObjectLine,'"','') AS ObjectLine
    FROM    ColonSplit AS X
)
SELECT  CAST(F.ObjectLine AS XML)
FROM    QuoteReplace AS F
FOR     XML PATH('object'), ROOT('root')


--COMMENTED OUT FOR TESTING
--SELECT    @JSONDATA AS JSONData, 
--      @ObjectArrayName AS ObjArrayName, 
--      @ObjectArrayNameStart AS ObjArrayNameStart, 
--      @ObjectArrayNameEnd AS ObjArrayNameEnd,
--      @ObjectArray AS ObjArray,
--      @ObjectArraySingleton AS ObjSingleton
--      --,CAST(@ObjectArray AS XML) AS XMLObjectArray

Answer 2

    select ca.TransactionID, ca.Success, ca.Response, cs.Values from 
    table_name tn with(NOLOCK)
    CROSS APPLY(select * from OPENJSON(tn.JSONColumnName) WITH(
    [TransactionID] nvarchar(50) '$.TransactionID',
    [Success] bit '$,Success',
    [Response] nvarchar(50), '$.Response',
    [Values] nvarchar(50) '$.Values'
    )) ca
    Where ca.Success = 0