Allow to specify an order clause when syncing a field JSON schema
- Dominant language
- Clojure
- Stars
- 49.3k
- Forks
- 6.8k
- Avg merge
- 1d 13h
- Merged PRs (30d)
- 653
Description
**Is your feature request related to a problem? Please describe.**
We are using a `JSONB` column to store historical events, and new fields can be added over time.
According to [the documentation](https://www.metabase.com/docs/latest/administration-guide/databases/postgresql.html#note-on-syncing-records-that-include-json), Metabase reads the 10k "first" rows of the table to infer the JSON fields from those (#708).
As it seems to use the on-disk order, it selects the oldest records in our table, so those will not include our newest fields.
**Describe the solution you'd like**
We would like to be able to configure an `ORDER` clause for Metabase to use when fetching the sample records.
We could them use `ORDER BY happened_at DESC` for example, so Metabase uses the most recent events, not the oldest ones. We could also use `ORDER BY random()`, although this is not optimised (see below for a possible alternative).
**Describe alternatives you've considered**
An alternative would be to be able to manually change the schema for the JSON field, so we can add our own sub-fields. But this would require us to do it manually.
Another alternative would be to have an option use the `TABLESAMPLE` keyword in the `FROM` clause, to use a pseudo-random sample of rows to infer the schema.
I can see both of those of those alternatives fitting some use-case in complement to the custom `ORDER` clause.
**How important is this feature to you?**
This is quite important as our internal reporting is made with Metabase and relies on various queries based on this events table.
Currently it requires us to convert all queries using JSON attributes added after the first 10k events to SQL, so we can `GROUP BY` those attributes.
Related to similar challenges with Mongo https://github.com/metabase/metabase/issues/15576#issuecomment-845798189
:arrow_down: Please click the :+1: reaction instead of leaving a `+1` or `update?` comment
Contributor guide
Research direction
Start with the PostgreSQL JSON syncing behavior described in the issue and the linked documentation, focusing on how the 10k sample records are selected. The work is done when users can configure an ORDER clause for that sample query and the configured ordering causes newer JSON fields to be inferred.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- clojure, postgresql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 42/100