JSONPath Expression: Is there any way to use it in Make? Example for Extracting Repetitive Nested Properties in an Array of Objects

I was wondering if I can use JSONPath Expression in Make?

JSONPath Expression is the XPath like syntax for JSON.

Some references about it:

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.

Hi :wave:t5:

This, unfortunately, is not possible in Make.

You can use the standard combination of “get” and “map” functions to extract specific objects from arrays. This, however, does not work for whole “paths” - it works only for specific levels.

A potential (untested) workaround might be to transform the whole object into a JSON string and use a “Text Parser - Match Pattern” module with some very advanced regular expression that could extract whole “question-answer” objects. :thinking:

Lastly, you might achieve it with the use of custom functions.
This feature is, however, available on the Enterprise pricing plan.


Cheerio :cat_roomba:

2 Likes