A JSON column that had become a schema

A settings JSON column added in 2019 for “flexible per-customer configuration”, containing forty keys three years later, four of which appear in WHERE clauses. The flexibility had done its job and had quietly become a schema that the database could not help with.

The symptom

$ mysql -e "EXPLAIN SELECT id FROM customers
            WHERE settings->>'$.tier' = 'enterprise'G"
         type: ALL
 possible_keys: NULL
         rows: 2088104
        Extra: Using where

$ mysql -e "SELECT JSON_LENGTH(settings) n FROM customers LIMIT 1"
40

$ ./bin/json-key-frequency settings
  tier                 in WHERE:  41,208 queries/day
  region               in WHERE:  12,884
  billing_day          in WHERE:   4,102
  feature_flags        in WHERE:     902
  ... 36 keys, never filtered on

Four keys out of forty carry every filter. That distribution is the whole finding: the column is not being used as a document, it is being used as four columns and thirty-six pieces of loosely structured data.

Why it happens

A JSON column is the right choice for data whose shape is genuinely unknown, and every key added to it is a small bet that nothing will need to filter on it. Some of those bets lose, and there is no moment at which the loss becomes visible.

The fix

Deciding by query log rather than by opinion

the four promoted, and why:

  tier          41,208 filters/day, 6 distinct values.
                → an ENUM column.
  region        12,884/day, 4 values, and a foreign key
                candidate.
                → a CHAR(2) with a constraint.
  billing_day    4,102/day, integer 1-28.
                → TINYINT.
  feature_flags    902/day, but an ARRAY — filtered with
                "does it contain X".
                → stays JSON, with a multi-valued index.

the 36 that stayed: read as a block, never filtered.

Generated columns, for the one that stays JSON

-- a stored generated column, indexed
ALTER TABLE customers
  ADD COLUMN tier_g VARCHAR(32)
    GENERATED ALWAYS AS (settings->>'$.tier') STORED,
  ADD INDEX idx_tier_g (tier_g);

-- STORED, not VIRTUAL: a virtual generated column can be
-- indexed too, and the index is maintained on write
-- either way. STORED costs disk and makes the read cheap.

-- and the multi-valued index for the array
ALTER TABLE customers ADD INDEX idx_flags (
  (CAST(settings->'$.feature_flags' AS CHAR(32) ARRAY))
);

The generated column was the interim step — it made the query fast in one migration without touching any application code, which bought time for the proper promotion. The multi-valued index is the permanent answer for the flags, because that key genuinely is a set and does not want to be a column.

The promotion, with a dual-write window

  release 1   add the columns, nullable. write BOTH the
              column and the JSON key. read JSON.
  backfill    batched, 50k at a time, from the JSON.
  release 2   read the column. still write both.
  release 3   stop writing the JSON key. add NOT NULL.
  cleanup     remove the key from existing rows:
              UPDATE customers SET settings =
                JSON_REMOVE(settings, '$.tier')
              — batched, and last.

four releases for one field. three fields at once, so
four releases total rather than twelve.

// the dual-write, and the assertion that they agree
public function setTier(Tier $tier): void
{
    $this->tier = $tier->value;                      // column
    $this->settings = [...$this->settings, 'tier' => $tier->value];
}

// a nightly job during the window
$mismatched = Customer::whereRaw("settings->>'$.tier' <> tier")->count();
// alerted if > 0. it was 0 throughout except for one
// day, caused by a bulk update that bypassed the model.

The nightly agreement check is what makes a dual-write window safe, and it caught exactly the failure it exists for — a bulk UPDATE written as raw SQL that touched the JSON and not the column. Without the check that divergence would have been discovered at cutover.

A CHECK constraint on what remains

ALTER TABLE customers ADD CONSTRAINT chk_settings CHECK (
  JSON_SCHEMA_VALID(
    '{"type":"object",
      "required":["notifications","locale"],
      "properties":{
        "locale":{"type":"string","pattern":"^[a-z]{2}_[A-Z]{2}$"},
        "feature_flags":{"type":"array","items":{"type":"string"}}
      },
      "additionalProperties":true}',
    settings
  )
);

additionalProperties: true keeps the flexibility that made a JSON column right in the first place while constraining the keys that have acquired meaning. Turning it to false would make this a schema in a text column, at which point the columns should just be columns.

Verifying it worked

$ mysql -e "EXPLAIN SELECT id FROM customers
            WHERE tier = 'enterprise'G"
         type: ref
          key: idx_tier
         rows: 412

# the endpoint that filters by tier
  p50   410ms → 8ms
  p95  2,140ms → 22ms

$ mysql -e "SELECT AVG(JSON_LENGTH(settings)) FROM customers"
36.0                    # was 40

$ mysql -e "SELECT COUNT(*) FROM customers
            WHERE JSON_CONTAINS_PATH(settings,'one','$.tier')"
0                       # the cleanup completed

Four hundred and twelve rows examined instead of two million is the whole result, and the zero at the end is the assertion that the migration actually finished — a cleanup pass that stops halfway leaves the column and the key both present, which is the state that produces a divergence a year later.

What this costs

Two places a field can live, and a rule about which. The rule is that a key filtered on becomes a column, and applying it requires somebody to be watching the query log — which nobody does until an endpoint is slow.

The CHECK constraint is also a schema definition in a string literal inside a migration, which no tooling validates and no IDE understands. Changing it means a new migration with the whole schema restated, and there is no diff that shows what changed except a careful read of two large strings.