4 ms·
I do the same. If I'm doing something where I'm storing JSON (e.g., from an external service), but only care about certain bits of it, I'll store the bits I ca
by Mister_Snuggles 10y ago
I do the same.
If I'm doing something where I'm storing JSON (e.g., from an external service), but only care about certain bits of it, I'll store the bits I care about in columns, then put the whole JSON object into a jsonb column.
The key advantage that I see is that when I decide that a new piece of the JSON needs to be cared about, I can do something like this:
(modify data types as required)
ALTER TABLE blah ADD COLUMN newthing text;
CREATE INDEX ON blah.newthing; -- if needed
UPDATE blah SET newthing = jsoncolumn->>'new_thing';
- takeda 10y agoJust a nitpick, but I believe it would be more efficient to create index as the last step here.
- brightball 10y agoNot necessarily. If the table is already fairly large you'd have to use Postgres CREATE INDEX CONCURRENTLY to avoid locking the table while it sorted out the index. The only downside is that you have very little control of the process. If you already have the data available in a JSONB column you can create the column, create the index and then set your app code to look to the JSON data if the column is null during transition while a background job goes through and populates rows either individually or in smaller batches to avoid disrupting user experience.
- Mister_Snuggles 10y agoThis was just an example of what you can do. I'd do this sort of thing while developing an application, but if it were in Production I'd take a much closer look at how the steps in my change would impact the running application.
- NickNameNick 10y agoyou should be able to index on an expression without adding the column. eg create index on blah((jsoncolumn->>'new_thing')); although I haven't tried it myself yet, I've been meaning to play with that feature.
- vog 10y agoYou don't even need these additional columns in PostgreSQL, thanks to expression indexes! [1] Just add an expression index for your new field: CREATE INDEX ON blah BTREE ((jsoncolumn->>'new_thing')); Then, the following kind of queries are blazingly fast due to the index: SELECT * FROM blah WHERE jsoncolumn->>'new_thing' = 'some_value'; This is less error prone than managing the consistency of the additional columns on your own. And if you still need some kind of pseudo-table, consider creating a simple view for that. [1] https://www.postgresql.org/docs/current/static/indexes-expressional.html https://www.postgresql.org/docs/current/static/indexes-expre...
- throwaway274739 10y agoWhy not just set the index right on the JSON column? Avoids the redundant column and potentially much smaller by only targeting rows which have a value for 'new_thing'. CREATE INDEX ON blah ((jsoncolumn->>'new_thing')) WHERE jsoncolumn ? 'new_thing';