Search object for any match of a string on any column

How To
#1

Is there any way to search a data object for any match of a string on any column? Ex: if i have a table with a first name and last name fields and have one entry of james davidson and another that david smith, i’d like to build an airscript query where searching for ‘dav’ would return both results.

1 Like
#2

hey @darren55! I feel the Filter List of JSON by List of Text UDF might be just what you’re after.

Check if out and let me know if:

  • You have a different use case
  • Need and enhanced version of the filter_by UDF

cheers!

1 Like
#3

Hey Juana, this is helpful. Would this work for my use case of something like the below? For example, I’m only interested in getting the phone numbers with +1714 but these are not lists of texts. Does your UDF support simple filters?

[
  {
    "name": "Darren",
    "phone": "+17145555555"
  },
  {
    "name": "john",
    "phone": "+17148888888"
  },
  {
    "name": "scott",
    "phone": "+14159999999"
  }
]
#4

hey @darren55 , thanks for your comment.

yeah, this bit right here should take care of analyzing phone values

TYPEOF(value) = "phone",
              LENGTH(value) != LENGTH(SUBSTITUTE(value, filter, ""))

And for the second part of your comment:

This is an advanced UDF, if you’d like to be able to use the proposed UDF with only one filter as an input, you can always set the filter list to:

["value"]

That being said, if you are only interested in having a simpler UDF that filters by only one filter, you can use a simplified version where filter = "+1714

FLAT(
  FROM
    item
  IN
    list
  SELECT DISTINCT
    FROM
      value,
      key
    IN
      item
    WHERE
      LENGTH(value) != LENGTH(SUBSTITUTE(value, filter, ""))
    SELECT
      item
)

Hope this helps!

2 Likes