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.
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.)
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?)