How to search using Grist APIs

[Techville_eServices]

Hello everyone,

I have list of more than 2000 products in a table. I want to search products using API. Please help me, how to do that?

[hera]

Do you have an integration tool, like Pabbly Connect they have a built in action for search records. Otherwise you need to make an api call like this:

See the api reference here: API reference - Grist Help Center

[Techville_eServices]

Thanks for the reply. I don’t have any automaton tool.

Here’s the example of my use case; Let suppose, I have table of products containing more than 2000 products. How to search products using api? I know, I can use ?filter=“product_name” :[“White Art Paper”]. But my question is, how can I search without using the full name? I mean using ‘paper’ instead of ‘white art paper’ and it should show all the products having the searched keyword in the product name.

[natalie-grist]

Hi There,

Unfortunately, there is not a way to search via API in this way at this time but would be great as a future improvement!

Thanks,

[paul-grist]

This probably won’t help you @Techville_eServices , but just in case.

It is possible to do full text search on Grist documents using SQLite’s full text support. We don’t have API support for this though, so it is only useful if you are self-hosting, or where periodic downloading is acceptable.

For example, if I were to download the class enrollment template, I could enable full-text search on some columns of one of its tables by installing sqlite-utils and then running:

sqlite-utils enable-fts Class\ Enrollment.grist Classes Semester Class_Name Class_Code --create-triggers

Now I can make full text queries either directly using the MATCH operator or by using sqlite-utils again:

$ sqlite-utils search Class\ Enrollment.grist Classes "Yoga"
[{"rowid": 3, "id": 3, "manualSort": 3, "Semester": "Spring 2019", "Class_Name": "Yoga Kids", "Class_Code": "2019S-Yoga", "Instructor": 4, "Times": "Sunday 10:00\u201311:00", "Days": "2019 Spring Sundays", "Start_Date": 1548547200, "End_Date": 1560038400, "Max_Students": 12, "gristHelper_Display2": "Toupe, Joby", "Count": 7, "Spots_Left": 5},
 {"rowid": 7, "id": 7, "manualSort": 7, "Semester": "Fall 2019", "Class_Name": "Yoga Kids", "Class_Code": "2019F-Yoga", "Instructor": 4, "Times": "Sunday 10:00\u201311:00", "Days": "2019 Fall Sundays", "Start_Date": 1567900800, "End_Date": 1575763200, "Max_Students": 12, "gristHelper_Display2": "Toupe, Joby", "Count": 8, "Spots_Left": 4}]

Again, I realize this is not going to help in a scenario where you need an API endpoint you can call against a live Grist document on our hosted service.

[enthus1ast]

I found that sqlite fulltext search is useless. In quite all “normal” use cases it produces bad results or does not work at all, or only if you lex and parse the input text, or do some low level sqlite api calls. I honestly does not know how people can use it.

I’ve played a little with https://typesense.org/ this does fulltext/fuzzy search quite well and easily, this is what most people want.

edit, some demo:

Typesense Documentation

[jperon]

With the introduction of the new SQL endpoint, the original request becomes very easy to implement. For example, with @paul-grist’s SQL widget showcased here, the following query gives all titles containing “ea”:

select
  Title, length(Title) as Len
from Songs
where Title GLOB "*ea*"
order by Len desc