Filter records by value used in ReferenceList when using API

Ask for Help
1

I want to make an API call such that it returns all records from my table which contain certain value in the column with the ReferenceList type. Is it possible?

2

You can do anything you like with the /sql endpoint. If you’ve tried with the ?filter option of /records and it isn’t working for you, then I think that will be the best option. Read the data with /sql with a plain SELECT * FROM YourTable, and see how the ReferenceList looks in SQL results, then create an appropriate WHERE clause to get just the rows you want.

3

It looks like

"type": "Buffer",
          "data": [
            91,
            2,
            0,
            0,
            0,
            117,
            1,
            0,
            0,
            0,
            76,
            105,
            5,
            0,
            0,
            0
          ]

How should I convert it to reference ids?

4

I think that is a “display” helper column which is a little weirdly encoded. You could make another column, maybe hidden, that reads out what you want in a friendlier format. I put an example here:
RefListSql - Grist
and then a query could be:
https://docs.getgrist.com/api/docs/7UNJ7qiJ5DJ8/sql?q=SELECT+*+FROM+Orders+WHERE+EXISTS+(SELECT+1+FROM+json_each(Readout)+WHERE+value+%3D+‘Cat’)

(If you use the POST variant of the /sql endpoint you can pass in the query and parameters in a more sensible way)

There’s doubtless a cleaner way to do it.

1 Like