JSON query

Question

This should be super simple, but I cannot get this to work. I want to extract routes data along with the trail id. Here is what I have
SET @jsonVariable = N'[
{

    "id": 1,

    "image": "trails/1.jpg",

    "name": "Countryside Ramble",


    "route": [

      {

        "stage": 1,

        "description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit."

      },

      {

        "stage": 2,

        "description": "Curabitur interdum molestie tempus."

      }]},

  {

    "id": 2,

    "image": "trails/2.jpg",

    "name": "Woodland Walk",



    "route": [

      {

        "stage": 1,

        "description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit."

      }]
}]

I removed some irrelevant info for now. I tried different variations of the syntax and I am unable to get information the way I want (e.g. route stage and description and Id of the trail.

Thanks in advance.

Answer 1

Hi @Naomi Nosonovsky ,

Assuming that you have SQL Server 2016 or later.

SQL

DECLARE @json NVARCHAR(MAX) =   
N'[  
 {  
 "id": 1,  
 "image": "trails/1.jpg",  
 "name": "Countryside Ramble",  
 "route": [  
 {  
 "stage": 1,  
 "description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit."  
 },  
 {  
 "stage": 2,  
 "description": "Curabitur interdum molestie tempus."  
 }  
 ]  
 },  
 {  
 "id": 2,  
 "image": "trails/2.jpg",  
 "name": "Woodland Walk",  
 "route": [  
 {  
 "stage": 1,  
 "description": "Lorem ipsum dolor sit amet, consectetur adipiscing elit."  
 }  
 ]  
 }  
]';  
  
SELECT JSON_VALUE(parent.value, '$.id') as id  
 , JSON_VALUE(parent.value, '$.image') as [image]  
 , JSON_VALUE(parent.value, '$.name') as [name]  
 , JSON_VALUE(child.value, '$.stage') as stage  
 , JSON_VALUE(child.value, '$.description') as [description]  
FROM OPENJSON(@json) AS parent  
 CROSS APPLY OPENJSON(parent.value, '$.route') AS child;