[jelder]

I thought this was common practice, generated columns for JSON performance. I've even used this (although it was in Postgres) to maintain foreign key constraints where the key is buried in a JSON column. What we were doing was slightly cursed but it worked perfectly.

[craftkiller]

If you're using postgres, couldn't you just create an index on the field inside the JSONB column directly? What advantage are you getting from extracting it to a separate column?

  CREATE INDEX idx_status_gin
  ON my_table
  USING gin ((data->'status'));

ref: https://www.crunchydata.com/blog/indexing-jsonb-in-postgres

[jelder]

That works for lookups but not for foreign key constraints.

[craftkiller]

Ah, makes sense. Thanks!

[cies]

..and it does not make "certain queries easier" (quote from the article).

[morshu9001]

You only need gin if you want to index the entire jsonb. For a specific attribute, you can use the default (btree) which I'm guessing is faster.

[a-priori]

Yes, as far as indices go, GIN indices are very expensive especially on modification. They're worthwhile in cases where you want to do arbitrary querying on JSON data, but you definitely don't want to overuse them.

If you can get away with a regular index on either a generated column or an expression, then you absolutely should.

[ramon156]

It works until you realize some of these usages would've been better as individual key/value rows.

For example, if you want to store settings as JSON, you first have to parse it through e.g. Zod, hope that it isn't failing due to schema changes (or write migrations and hope that succeeds).

When a simple key/value row just works fine, and you can even do partial fetches / updates

[jelder]

The necessity of using a JSON column was outside of my control, but Zod etc. are absolutely required, I think, in most projects. I wrote more about that here: https://www.jacobelder.com/2025/01/31/where-shift-left-fails...

[mickeyp]

EAV data models are kinda cursed in their own right, too, though.

[morshu9001]

Doesn't sound very cursed, standard normalized relations for things that need it and jsonb for the big bags of attributes you don't care to split apart

[sigwinch]

It is. I’d wondered if STORED is necessary and this example uses VIRTUAL.

[jasonthorsness]

This is the typical practice for most index types in SingleStore as well except with the Multi-Value Hash Index which is defined over a JSON or BSON path