Trouble Filtering JSON with SQL: Names with Lastnames Disappear on Space Input

Hello everyone! I'm facing a challenge while attempting to filter through names in a JSON using SQL. I'd appreciate any insights or suggestions on this.

I have a dataset where names and surnames might be present in a single column. For example:

  • Leticia Aguilar Rodriguez
  • Leticia Garza Tames
  • Leticia Alondra Solano Martinez

When I type "Leticia" followed by a space in a search bar, I want the system to continue showing all names starting with "Leticia", irrespective of whether they have a second name. This allows me to further filter by surnames or any subsequent names.

Currently, upon hitting space after "Leticia", entries like "Leticia Garza Tames" disappear from the results. I have tried various SQL query structures to address this, but without success.

Query Tried:
I've been working with the following structure:

FROM {{ }}
  lower(nombre) LIKE lower({{textInput8.value}}) || '%' OR
  lower(apellidos) LIKE substr(lower({{textInput8.value}}), instr(lower({{textInput8.value}}), ' ') + 1) || '%'

(Note: nombre refers to the name column and apellidos refers to the last name.)

Any help would be greatly appreciated. Thanks in advance!

Hi @Adorp, welcome to the community :wave:

Have you tried using the native search functionality of the new table component?
Screenshot 2023-08-24 at 10.17.21 am

Not sure if you're needing the result from that search but if you're only goal is to display the related search term results, this would be a better approach to it. It searches the string on multiple string columns of that table so you don't have to specify any columns there.

Not sure with the issue you have as I tried that on my instance and it still showed all three Leticia's after the space.


Hey @Adorp! This code should work. As @jocen mentioned, there is fuzzy search natively in the table, but it would not be able to search one string value across multiple columns. I've showed this below with 2 tables and 1 text input.

The table on the left is using the results of the Query JSON w/ SQL query, the one on the right is using the table's built in fuzzy search feature. Let me know if you are still having issues!


Query JSON w/ SQL

1 Like

Wow, I really appreciate your response! Thanks for adding a GIF with the names I was using. Thanks Joe, I will try this now.

1 Like