How to parse JSON string not object

Question

There is a VARCHAR column jsonString that has simple text that is formatted as JSON:

id         jsonString
1          {"topData":{"personName":"john","personAge":"20"}}
2          {"topData":{"personName":"mike","personAge":"30"}}
3          {"topData":{"personName":"sten","personAge":"50"}}

How to parse this string? It is not a JSON object, so do we need to cast it as JSON first?

select OPENJSON(cast(jsonString as JSON)

I have tried many ways but couldn't extract personName and personAge as separate values.

According to the docs learn.microsoft.com/en-us/sql/t-sql/functions/…, "jsonExpression Is a Unicode character expression containing JSON text.". So there is no need to cast your string to anything at all. — Nick.Mc
– Nick.Mc, Commented Feb 28, 2021 at 22:54

Johnatha Relvas · Accepted Answer · 2021-02-28 23:08:57Z

1

Try something like this:

SELECT personName,
       personAge 
FROM NAME_OF_THE_TABLE
OUTER APPLY OPENJSON (jsonString)
WITH
(
    personName nvarchar(50) '$topData.personName',
    personAge tinyint '$topData.personAge'
)

You didn't mention the name of the table, so you can replace the NAME_OF_THE_TABLE by the real one

edited Feb 28, 2021 at 23:08

answered Feb 28, 2021 at 23:00

Johnatha Relvas

3361 silver badge8 bronze badges

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

Collectives™ on Stack Overflow

How to parse JSON string not object

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related