JSONata Advanced Functions: $filter, $exists, $not, $map

Community Resources
, , ,
1

Glide uses JSONata in to query JSON in the Query JSON column.

I’ve understood the simple syntax of mapping through arrays and objects.

  • $rows[2].Name - Returns the name of the 3rd item in the array
  • $rows[].Company.Name - Returns all Company Names in the array

However using JSONata functions has been challenging, but I figured on one example I’ll share with you here. I wanted to take all the rows returned from Glide’s Get Rows endpoint and get the Glide row IDs of all rows with an empty value in a column. Here’s how to build it.

Sample JSON

[ { "rows":[ { "$rowID":"qo9E.QS4StCnt-l8NGq3RA", "tQowu":"chvcRSSPTJaaoK2zbhGplQ", "$rowIndex":"W4", "Name":"Darren Test", "7gs1N":"Concert", "yTkLn":"Circus", "XsO9S":"Candy", "Fi3FK":"People pay for the app", "SlTnS":"They abandoned the project", "1TME8":"They have users", "EG3td":"They are onboarding their team", "g1tSc":"They won't respond", "LJwsZ":"They are excited", "McQlI":"The are rigid", "GQca3":"They are skeptical" }, { "$rowID":"8rlmKXWlT2a05x3qPw851A", "$rowIndex":"WI", "Name":"sdfg" }, { "$rowID":"a.1W8y-MCQbqYMuhpaslnxg", "$rowIndex":"WD", "Name":"Namne" }, { "$rowID":"P3sGMXI6Q.S5HCIUhfWGiQ", "tQowu":"chvcRSSPTJaaoK2zbhGplQ", "$rowIndex":"W5", "Name":"Acme Inc" }, { "$rowID":"UEFhY5xyQjeWWp.i.FB2XQ", "tQowu":"chvcRSSPTJaaoK2zbhGplQ", "$rowIndex":"WL", "Name":"go" }, { "$rowID":"fz3MGJ7AS7KUx7WZ-kZGjA", "$rowIndex":"WG", "Name":"goober" }, { "$rowID":"a.MLAIGa6RzKe83ZjzpyM9Q", "tQowu":"chvcRSSPTJaaoK2zbhGplQ", "$rowIndex":"WF", "Name":"dfghdfgh" }, { "$rowID":"7LeoFzHqQ22jSvV2HN8neA", "$rowIndex":"WH", "Name":"dddd" }, { "$rowID":"X88ibzdNTbizii06PYNczw", "$rowIndex":"WE", "Name":"good" }, { "$rowID":"L908hzGySa-r58CCT3knNQ", "$rowIndex":"WJ", "Name":"uuuu" }, { "$rowID":"FcjD8DNMQYGy026zEsoo9g", "tQowu":"chvcRSSPTJaaoK2zbhGplQ", "$rowIndex":"WK", "Name":"just" } ] } ]

JSONata Query

$map(
   $filter(
      $.rows, 
      function ($v) {
         $not(  $exists($v."tQowu")  )
      }
   ),
   function($v) {
       $v."$rowID"
   }
)
  • $.rows gets the array of rows from the api response
  • $exists returns true if the "tQowu" field is not empty
  • $not negates what’s returned from $exists allowing me to check if the field IS empty
  • $filter checks all rows with this logic only returning rows where the field is empty
  • $map maps the rows to the $rowID

Note: $map isn’t required in this use case, this also works:

   $filter(
      $.rows, 
      function ($v) {
         $not(  $exists($v."tQowu")  )
      }
   )."$rowID"
5 Likes