Azure SQL - Handling JSON column in the table

Question

Hi,

I want to convert table content which contain json value data in one of the column in Azure SQL server. Like below,

Country State Json_value
US Alabama {"name":"John","surname":"Doe","age":45}
Canada Toronto {"name":"Mark","surname":"Bagwell","age":35}

I need to convert as below in the query itself.

Country State name surname age
US Alabama John Doe 45
Canada Toronto Mark Bagwell 35

Is this can be achieved in SQL server query using json function. kindly help me in resolving this.

Answer 1

Hi @Mohan Kumar R S , thanks for reaching out to us. we are glad to assist you here. Instead of using 'openjson' function please try below sql query to achieve the desired result:

select country, state, JSON_VALUE(jsonvalue,'$.name') as firstname,JSON_VALUE(jsonvalue,'$.surname') as surname, JSON_VALUE(jsonvalue,'$.age') as age from YourTableName;

Please note that the json properties are case sensitive, so use accordingly.

----------

If an Answer is helpful, please “Accept Answer” or Up-Vote for the same which might be beneficial to other community members reading this thread.