CODEWITHSUNDEEP
jsonsql-server-2016
SQLDev
SQLDev

Reputation: 65

Issue with Open Json returning null

I have a JSON structured like this:

{
"Assessment": {
    "AssessmentContext": {
        "ReportID": "1021060",
        "ReportName": "John Smith",
    },
}

I am using SQL server 2016 and this is my query:

SELECT Data.ReportID,Data.ReportName
FROM TableName DT
CROSS APPLY 
OPENJSON(DT.TX_TEXT) WITH (
ReportID INT '$.ReportID',
ReportName VARCHAR(100) '$.ReportName'
    ) AS Data

I am getting NULL values for both the columns when I run this query. What am I doing wrong?

Update:

I am editing the question as I am not able to format the text as code in comment. How do I tweak my sql to show Report Id, Report Name, Item Name, Value.

{
  "Assessment": {
"AssessmentContext": {
    "ReportID": "1021060",
    "ReportName": "John Smith",
},
},       

"Assessment": {
        "AssessmentID": "0",
        "AssessmentItems": [{
                "ItemName": "A",
                "Value": "N"                    
            }, {
                "ItemName": "B",
                "Value": "A"                    
            }]
}

Upvotes: 1

Views: 396

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could pass path:

SELECT Data.ReportID,Data.ReportName
FROM TableName DT
CROSS APPLY 
OPENJSON(DT.TX_TEXT, '$.Assessment.AssessmentContext') -- here
WITH ( ReportID INT '$.ReportID',
       ReportName VARCHAR(100) '$.ReportName') AS Data

db<>fiddle demo

Upvotes: 1

Related Questions