Issue with Open Json returning null

Question

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"                    
            }]
}

Lukasz Szozda · Accepted Answer · 2019-06-17 15:29:33Z

1

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

answered Jun 17, 2019 at 15:29

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQLDev Over a year ago

Thanks so much, it worked. Never knew it was so simple duh.

Collectives™ on Stack Overflow

Issue with Open Json returning null

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related