Faceted browse against a JSON list of tags

[simonw]

If a table has a ["foo", "bar", "baz"] JSON column allow that to be faceted against.

• Support ?column__arraycontains=x filter queries
• Support ?_facet_array=column faceting

[simonw]

Example table: https://latest-code.datasette.io/code/definitions

Here's a query that does facet counting against that column:

https://latest-code.datasette.io/code-a26fa3c?sql=select+count%28*%29+as+n%2C+j.value+from+definitions+join+json_each%28params%29+j+group+by+j.value+order+by+n+desc%3B

select count(*) as n, j.value
from definitions join json_each(params) j
group by j.value order by n desc;

[simonw]

And here's the query for pulling back every record tagged with a specific tag:

https://latest-code.datasette.io/code-a26fa3c?sql=select+*+from+definitions+where+rowid+in+%28%0D%0A++select+definitions.rowid%0D%0A++from+definitions+join+json_each%28params%29+j%0D%0A++where+j.value+%3D+%3Atag%0D%0A%29&tag=filename

select * from definitions where rowid in (
  select definitions.rowid
  from definitions join json_each(params) j
  where j.value = :tag
)

[simonw]

Note that there doesn't seem to be a way to use indexes (even indexes on expressions) to speed these up, so this will only ever be effective on smaller data sets, probably in the 10,000-100,000 range. Datasette is often used with smaller data sets so this is still worth pursuing.

[simonw]

I'm going to go with ?_facet_array=definitions as the querystring argument for this.

And ?definitions_arraycontains=foo as the filter argument.

[simonw]

Challenge: facets can also be defined in metadata.json like this:

{
  "databases": {
    "sf-trees": {
      "tables": {
        "Street_Tree_List": {
          "facets": ["qLegalStatus"]
        }
      }
    }
  }
}

But... ?_facet_array=definitions doesn't fit in that data structure. Need to have an alternative mechanism for defining this kind of facet.

[simonw]

Demo: https://latest.datasette.io/fixtures/facetable?_facet_array=tags#facet-tags