Heh, this is the reason Takahe is currently broken on SQLite. Hashtags currently need a JSON contains.

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.

Django would need to be patched to do that, since the default SQLite provider would make it hard to do that cleanly.

Here’s the relevant Takahe issue: https://github.com/jointakahe/takahe/issues/325

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

Do these developers, who require JSON CONTAINS, know about basic normal forms and RDBMS in general?!

I understand, that sometimes for performance it is needed to de-normalize huge datalakes, but typical website controlled by CMS is not huge by any means.