4 ms·
> What's the difference, functionally speaking? (Asking honestly, not being snarky -- I may not understand what you are saying / what the equivalent postgres fe
by hans_castorp 7y ago
> 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 7y agoInteresting, 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.)