Search with multiple optional fields

What is the correct query syntax to use when searching with multiple fields, all of which are optional?

Example:
Search Params: First Name, Last Name, ID, Zip Code (all these fields have corresponding columns in the table im searching in.

A user will enter one or more of those fields into a search form and I need to query with those different params. I could use First Name only or Last Name and ID, or Zip Code and First Name, etc, etc.

You probably want something like this:

MERGE_OBJECTS(
  [
    IF(ISNOTEMPTY(first_name), { "first_name": { "eq": first_name } }),
    IF(ISNOTEMPTY(last_name), { "last_name": { "eq": last_name } }),
    IF(ISNOTEMPTY(zip_code), { "zip_code": { "eq": zip_code } }),
    IF(ISNOTEMPTY(id), { "id": { "eq": id } })
  ][?(ISNOTEMPTY(@))]
)

Add any additional fields you need to the expression. Then use the output of this expression as a filter.

1 Like

Kyung - thanks for the input! I gave that shot. apologies, im using different input values, but the spirit is identical.

I modified your query a bit, but didn’t get the desired result. Screenshots below:

this is my search criteria - I’m looking for a loan associated with Mike Cammon as the customer

i use this query - find loans where first name = mike and last name = cammon

my result though is that I get all results that match just on first name, and it doesn’t even evalutate the second condition for last name (i trimmed the majority of the details fyi)
{
“FirstName”: “mike”,
“LastName”: “cammon”,
}
{
“FirstName”: “mike”,
“LastName”: “smith”,
}

Another odd note: when I change the OR to AND, I get every record from the table with no filtering

Can you run your expression in a transform and post an image? You should see something like:

{
  "FirstName": { "eq": "mike" },
  "LastName": { "eq": "cammon" }
}

The result of the expression should be used as a filter in the AirData query.

I copied this expression into a Transform and the result was NULL

IF(ISNOTEMPTY(search.first_name), { "FirstName": { "eq": search.first_name } })
  AND IF(ISNOTEMPTY(search.last_name), { "LastName": { "eq": search.last_name } })
  AND IF(ISNOTEMPTY(search.loan_id), { "LoanID": { "eq": search.loan_id } })
  AND IF(ISNOTEMPTY(search.originator), { "Originator": { "eq": search.originator } })

When run the first part of the expression

IF(
  ISNOTEMPTY(search.first_name),
  { "FirstName": { "eq": search.first_name } }
)

my result is:

{
  "FirstName": {
    "eq": "mike"
  }
}

I ended up re-trying the original with small mod and its working as expected:

MERGE_OBJECTS(
  [
    IF(
      ISNOTEMPTY(search.first_name),
      { "FirstName": { "eq": search.first_name } }
    ),
    IF(
      ISNOTEMPTY(search.last_name),
      { "LastName": { "eq": search.last_name } }
    ),
    IF(
      ISNOTEMPTY(search.loan_id),
      { "LoanID": { "eq": search.loan_id } }
    ),
    IF(
      ISNOTEMPTY(search.originator),
      { "Originator": { "eq": search.originator } }
    )
  ][?(ISNOTEMPTY(@))]
)
1 Like