No matching implementation for `.contains`

[parrotmac]

Summary

The JavaScript version of this library has a .contains method. This enables (among other things) queries based on the value of a JSON object nested within an Array.

Problem statement

Given a table with the following structure and some sample data:

Table movies

id	data
2	{"title": "The Matrix", "cast": [{"name": "Keanu Reeves", "role": "Neo"}, {"name": "Laurence Fishburne", "role": "Morpheus"}], "release_year": 1999}}
5	{"title": "John Wick", "cast": [{"name": "Keanu Reeves", "role": "John Wick"}, {"name": "Ian McShane", "role": "Winston"}], "release_year": 2014}
9	{"title": "Cars", "cast": [{"name": "Owen Wilson", "role": "Lightning McQueen"}, ...], "release_year": 2006}

It should be possible to query for rows based on the contents of an object within the nested JSON array. Using plain PostgreSQL it's possible with a query such at the following:

SELECT *
FROM movies
WHERE data->'cast' @> '[{"name": "Keanu Reeves"}]'

Using the Python client, the closest I've managed to construct is

supabase.table("movies").select("*").cs('data->cast', ['[{"name": "Keanu Reeves"}]'])

However, this fails and it doesn't seem possible to use the cs operator as it stands because

• the colons are filtered here
• the query is wrapped in {} even when that’s not appropriate here.

However... the JavaScript client does work here! The following produces the expected result:

supabase.from("movies").select("*").contains("data->cast", '[{"name": "Keanu Reeves"}]')

I believe the biggest difference between the Python .cs() and the JavaScript .contains is the filtering done in the Python client that's missing in the JS client. (Source for JS .contains)

Proposal

Implement the .contains and .containedBy methods from the JS client here in the Python client. To mach behavior I believe this would include skipping the sanitizing step currently defined in the Python client and doing some type checking to determine if the query should be wrapped (e.g. in {}).