I was wondering if I can use JSONPath Expression in Make?
JSONPath Expression is the XPath like syntax for JSON.
Some references about it:
- JSONpath - Introduction And Syntax (jsontostring.com)
- JSONPath Expression Tester (curiousconcept.com)
- JSONPath: Query expressions for JSON (ietf.org)
Example of usage
I have this JSON
{
"Event":"schedule",
"Lead":{
"Id":17017487,
"Company":"Nienow-Carter",
"Industry":{
"id":114452,
"value":"N/A"
},
"Origin":{
"id":63098,
"value":"N/A"
},
"SubOrigin":{
},
"Site":"1",
"Stages":[
{
"StageId":46909,
"Stage":"Stage 1",
"LeadQualification":"Very Hot",
"ReviewDate":"2023-10-06T03:00:30.8933524",
"QuestionAnswers":[
{
"Question":"1. Is a qualified Lead?",
"Responses":[
{
"Response":"Yes"
}
]
}
]
},
{
"StageId":57971,
"Stage":"Stage 2",
"LeadQualification":"Very Hot",
"ReviewDate":"2023-10-06T03:01:47.1715891",
"QuestionAnswers":[
{
"Question":"1. It has receptionist?",
"Responses":[
{
"Response":"Yes"
}
]
},
{
"Question":"2. Did they knew about us from where?",
"Responses":[
{
"Response":"Online",
"Questions":[
{
"Question":"2. 1. Where?",
"Responses":[
{
"Response":"Facebook"
},
{
"Response":"Google"
}
],
"Questions":[
{
"Question":"2. 1. 1. Where?",
"Responses":[
{
"Response":"Facebook"
},
{
"Response":"Google"
}
]
},
{
"Question":"2. 1. 2. When?",
"Responses":[
{
"Response":"2023-02-01"
}
]
}
]
},
{
"Question":"2. 2. When?",
"Responses":[
{
"Response":"2023-02-01"
}
]
}
]
}
]
},
{
"Question":"3. SSN (Social Security Number) ?",
"Responses":[
{
"Response":"1"
}
]
},
{
"Question":"4. Amount?",
"Responses":[
{
"Response":"$ 5000,00"
}
]
}
]
}
],
"UserId":372100,
"CurrentCycle":1,
"CreatedAt":"2023-10-06T02:59:37.819Z",
"UpdatedAt":"2023-10-06T03:02:08.077Z"
}
}
And I need to extract all the Question and Response of the StageId= 57971, even the nested Questions
Using JSONPath Expression, I can write:
$.Lead.Stages[?(@.StageId==57971)]..*[?(@.Question)]
And the result will be:
[
{
"Question":"1. It has receptionist?",
"Responses":[
{
"Response":"Yes"
}
]
},
{
"Question":"2. Did they knew about us from where?",
"Responses":[
{
"Response":"Online",
"Questions":[
{
"Question":"2. 1. Where?",
"Responses":[
{
"Response":"Facebook"
},
{
"Response":"Google"
}
],
"Questions":[
{
"Question":"2. 1. 1. Where?",
"Responses":[
{
"Response":"Facebook"
},
{
"Response":"Google"
}
]
},
{
"Question":"2. 1. 2. When?",
"Responses":[
{
"Response":"2023-02-01"
}
]
}
]
},
{
"Question":"2. 2. When?",
"Responses":[
{
"Response":"2023-02-01"
}
]
}
]
}
]
},
{
"Question":"3. SSN (Social Security Number) ?",
"Responses":[
{
"Response":"1"
}
]
},
{
"Question":"4. Amount?",
"Responses":[
{
"Response":"$ 5000,00"
}
]
},
{
"Question":"2. 1. Where?",
"Responses":[
{
"Response":"Facebook"
},
{
"Response":"Google"
}
],
"Questions":[
{
"Question":"2. 1. 1. Where?",
"Responses":[
{
"Response":"Facebook"
},
{
"Response":"Google"
}
]
},
{
"Question":"2. 1. 2. When?",
"Responses":[
{
"Response":"2023-02-01"
}
]
}
]
},
{
"Question":"2. 2. When?",
"Responses":[
{
"Response":"2023-02-01"
}
]
},
{
"Question":"2. 1. 1. Where?",
"Responses":[
{
"Response":"Facebook"
},
{
"Response":"Google"
}
]
},
{
"Question":"2. 1. 2. When?",
"Responses":[
{
"Response":"2023-02-01"
}
]
}
]
So, even the expression maintaining “Questions” that are in a depth structure (Ex: 2. 1. , 2. 2. , 2. 1. 1. or 2. 1. 2.). It has extracted all of them and placed them on the first level, enabling it to iterate over all questions using just the first nested level of the array, avoiding the need to know previously how many nested levels will exist.