[evanelias]

> If you want to support several different expressions you need to create a new column each time

Yes and no. Generated columns in MySQL can optionally be "virtual". An indexed virtual column is functionally identical to an index on an expression.

> Additionally, an ALTER TABLE blocks access to the table.

It depends substantially on the specific ALTER and version of MySQL. Many ALTERs do not block access to the table in modern MySQL; some are even instantaneous.

> But MySQL doesn't support indexing the complete JSON value for arbitrary queries. You can only index specific expressions by creating a computed column with that expression and indexing that.

What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)

[hans_castorp]

> What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres feature is?)

You create a single index, e.g:

create index on the_table using gin(jsonb_column);

And that will support many different types of conditions,

e.g.: check if a specific key/value combination is contained in the JSON:

where jsonb_column @> '{"key": "value"}'

this also works with nested values:

where jsonb_column @> '{"key1" : {"key2": {"key3": 42}}}'

Or you can check if an array below a key contains one or multiple values:

where jsonb_column @> '{"tags": ["one"]}' or where jsonb_column @> '{"tags": ["one", "two"]}'

Or you can check if all keys from a list of keys are present:

where jsonb_column ?& array['key1', 'key2']

All those conditions are covered by just one index.

[evanelias]

Interesting, thanks! Indeed, there isn't an equivalent feature in MySQL yet.

Out of curiosity, how commonly is this used at scale? I'd imagine there are significant trade-offs with write amplification, meaning it would consume a lot of space and make writes slow. (vs making indexes on specific expressions, I mean. That said, you're right -- there are definitely use-cases where making indexes on specific expressions isn't practical or is too inflexible.)