It's possible to simulate JSON contains in SQLite using a devious subselect.

Here's an example:

https://datasette.io/content/plugin_repos?_facet_array=tags&...

That page shows every row where the tags column (a SQLite JSON list of strings) contains the string "Data Import"

Here's the underlying SQL:

https://datasette.io/content?sql=select%0D%0A++rowid%2C%0D%0...

    select
      rowid,
      repo,
      tags
    from
      plugin_repos
    where
      :tag in (
        select
          value
        from
          json_each(plugin_repos.tags)
      )
    order by
      rowid
    limit
      101

I haven't tried it yet, but I have a strong hunch that this could be dramatically accelerated using a join against a FTS table on that "tags" column in order to first filter the rows down to a likely subset.

Yep. That’s exactly what I stumbled upon myself.