UDFs - Filter List of JSON by List of Text (à la CONTAINS() or "like")

Alright! There’s tons of ways we might need to process a list of items in, today we’ll have a ton + 1


Use Case

We have the following data (this is an input)

[
  {
    "name": "HIIT Burner",
    "length": 30,
    "equipment": [
      "Kettlebell",
      "TRX",
      "Dumbbell",
      "Rope"
    ],
    "phone": "+17145555555",
    "email": "ron@ripped.com",
    "smsAllowed": true
  },
  {
    "name": "TRX Killer",
    "length": 90,
    "equipment": [
      "TRX",
      "Rope",
      "Yoga Block"
    ],
    "phone": "+12123347089",
    "email": "mary@globogym.com",
    "smsAllowed": false
  },
  {
    "name": "Kettlebell Master",
    "length": 45,
    "equipment": [
      "Free Weights",
      "Resistance Band"
    ],
    "phone": "+13043173027",
    "email": "stacy@xfitworld.com",
    "smsAllowed": true
  },
  {
    "name": "Sweaty Animal",
    "length": 20,
    "equipment": [
      "Body Weight",
      "Rope"
    ],
    "phone": "+14159999999",
    "email": "chad@gymbros.com",
    "smsAllowed": false
  },
  {
    "name": "Hardcore Yoga",
    "length": 20,
    "equipment": [
      "Kettlebell",
      "Medicine Ball"
    ],
    "phone": "+17148888888",
    "email": "lily@multigym.com",
    "smsAllowed": false
  }
]

We need to return only the objects whose one or more values match (partially or completely) one (or more) of a given list of values.

For this use case, the list of values to check for also needs to be an input, here’s a sample list:

[
  true,
  "TRX"
]

Expected output

Given the Sample Data and List of Values above, the expected output is:

[
  {
    "name": "HIIT Burner",
    "length": 30,
    "equipment": [
      "Kettlebell",
      "TRX",
      "Dumbbell",
      "Rope"
    ],
    "phone": "+17145555555",
    "email": "ron@ripped.com",
    "smsAllowed": true
  },
  {
    "name": "Kettlebell Master",
    "length": 45,
    "equipment": [
      "Free Weights",
      "Resistance Band"
    ],
    "phone": "+13043173027",
    "email": "stacy@xfitworld.com",
    "smsAllowed": true
  },
  {
    "name": "TRX Killer",
    "length": 90,
    "equipment": [
      "TRX",
      "Rope",
      "Yoga Block"
    ],
    "phone": "+12123347089",
    "email": "mary@globogym.com",
    "smsAllowed": false
  }
]

UDF

Let’s dig into this (somewhat complex) logic.

UDF Setup

Inputs

  • list Datatype: List of JSON - Input data to process
  • filters Datatype: List of Text - List of values to filter our list

Output

  • Datatype: List of JSON

UDF Body

For each object in our list of data, check if any of its values contains the filter value, and return the object (without duplicates) or, in pseudo-code:

forEach filter in filters:
 forEach object in list:
  forEach value in object:
   if value like "filter"
return unique object

Now let’s translate the above to Airscript:

FROM
  workout
IN
  FLAT(
    FROM
      filter
    IN
      filters
    SELECT DISTINCT
      IF(
        TYPEOF(filter) = "boolean",
        FLAT(FROM item IN list SELECT DISTINCT FROM value, key IN item WHERE value = filter SELECT DISTINCT item),
        FLAT(
          FROM
            item
          IN
            list
          SELECT DISTINCT
            FROM
              value,
              key
            IN
              item
            WHERE
              IF(
                TYPEOF(value) = "string" OR TYPEOF(value) = "email",
                LENGTH(value) != LENGTH(SUBSTITUTE(LOWERCASE(value), LOWERCASE(filter), "")),
                TYPEOF(value) = "list",
                LENGTH(JOIN(value, ",")) != LENGTH(SUBSTITUTE(LOWERCASE(JOIN(value, ",")), LOWERCASE(filter), "")),
                TYPEOF(value) = "phone",
                LENGTH(value) != LENGTH(SUBSTITUTE(value, filter, ""))
              )
            SELECT DISTINCT
              item
        )
      )
  )
SELECT DISTINCT
  workout

All done! Feel free to leave a thumbs up or a comment if you got any questions.


:information_source: This is just one of many ways this same use case can be implemented. Play around with other Airscript functions, or enhance the above to filter by workout length!

4 Likes

OMG, I was just looking for this exact scenario - thank you so much!!

1 Like