Menu
After upgrading to PostgreSQL 9.4, how do I convert all my JSON columns into JSONB columns?
I don't mind losing any duplicate keys and whitespace.
395
There are two ways to accomplish this: Simply concatenating the new key/value pair: update the_table set attr = attr || '{"is_default": false}'; This works because when concatenating two jsonb values, existing keys will be overwritten.
Json processes input faster than jsonb as there is no conversion involved in this. Jsonb converts the JSON data into the binary form so it has slightly slower input due to the binary conversion overhead. There is no change in the Schema design while working with JSON.
The json data type stores an exact copy of the input text, which processing functions must reparse on each execution; while jsonb data is stored in a decomposed binary format that makes it slightly slower to input due to added conversion overhead, but significantly faster to process, since no reparsing is needed.
JSONB supports indexing the JSON data, and is very efficient at parsing and querying the JSON data. In most cases, when you work with JSON in PostgreSQL, you should be using JSONB.
ALTER TABLE t ALTER COLUMN j TYPE jsonb USING j::text::jsonb; In the context of Rails, here is an ActiveRecord migration alternative:
def change reversible do |dir| dir.up { change_column :models, :attribute, 'jsonb USING CAST(attribute AS jsonb)' } dir.down { change_column :models, :attribute, 'json USING CAST(attribute AS json)' } end end I tested this on a table with 120 000 records, each record having four json columns and it took me about a minute to migrate that table. Of course, it depends on how complex the json structure is.
Also, notice that if your existing records have a default value of {}, you have to add to the above statements default: {}, because otherwise you'll have jsonb columns, but the default value will remain as '{}'::json.
35
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
