Json SQL Server query

user_7764674 0
Viewed 33
{
    "PayloadDateTime": "2022-03-16T20:12:23.8139785+00:00",
    "Payload": "{
                    "ClientId": "23434",
                    "LetterType": "MyLetter",
                    "TransactionId": "7f51d120-3c6f-4b10-90a5-51ab39dd949b",
                    "LoanNumber": "4543554",
                    "EmailAddress": "xyz@gmail.com",
                    "EmailSent": true,
                    "Result": "Success",
                    "ErrorMessage": ""
                 }"
}

The above Json object is stored in a column of a SQL Server table.

I am trying the query

SELECT TOP (1000)  
    JSON_VALUE([MyColumn], '$.PayloadDateTime') AS Name, *
FROM 
    [dbo].[MyTable]

which works fine

JSON_VALUE([MyColumn], '$.Payload') returns the value of the internal json object.

But when I am trying to get the value of the Json in Payload (example below), it returns null.

JSON_VALUE([EmailServicingSystemResult], '$.Payload.ClientId')

The Payload JSON looks like this

"Payload": "{}"

whereas normally it is like this

"Payload": {}

How can I query the elements of the inner Json elements of Payload? Any suggestions?

sql-server json
1 Answers

Would you want to do something like this:

JSON_VALUE(JSON_VALUE([MyColumn], '$.Payload'), '$.ClientId')

user_76679460
Related