Has anyone actually used json-based queries in PG in a nontrivial way, and had a good experience? I looked into it but the SQL syntax for doing json operations was crazy complicated, and I had trouble finding an ORM that did a good job of simplifying it.
Yeah, I don't mind it. Oftentimes when you want to manipulate JSON, you'll embed a raw snippet of PG-specific SQL into the query you're building with your ORM. I do wish the operators were a little more ergonomic though. One of these days I'll print out the list and stick it to my monitor.
What kind of non-trivial use cases do you have. I've found you generally just need to pick out a field using the JSON syntax, then you can use all of the normal SQL tools for fancier stuff...
For millions plus rows, the queries get crazy slow. If your jsonb field contains lot of data, JOINs on toast storage tables takes chunk of the query times.
My 2 cents: Best to keep data normalized and in separate columns. If you gotta keep data in jsonb, keep only the data that you don't need to query on. Anything you need to query, you better put it as a column. You'll thank me later.
