ActiveRecord: store/JSONB
ActiveRecord: store/JSONB looks at how one Rails app uses Postgres jsonb columns. It starts with the migration that converted bilingual string columns to jsonb with jsonb_build_object, and the down method that reverses it. It tries store_accessor on a disposable table and explains why the app wrote its own Translatable concern. It then queries jsonb in SQL with ->> and COALESCE, examines what GIN and trigram indexes speed up, and shows jsonb holding OAuth payloads and arrays of hints.
What you'll learn
- ✓Converting string columns to jsonb in a reversible migration with jsonb_build_object and ->>
- ✓How store_accessor works on a jsonb column, and why symbol keys return nil after a reload
- ✓Why a hand-written Translatable concern handles the pt-BR locale and repeated keys, and its dirty tracking cost
- ✓Querying jsonb in SQL with ->>, -> and COALESCE for a locale fallback
- ✓Which operators a default GIN index accelerates, and when pg_trgm expression indexes fit
- ✓Using jsonb for data whose shape varies, such as OAuth payloads and arrays of hashes
The migration that turned the bilingual columns into JSONB
Catalog::Course#title and #description, Catalog::Lesson#title and #content_markdown, Catalog::Challenge#instruction, #solution and #hints: every one of these columns is jsonb. Most of them didn't start that way. courses.title, lessons.title, lessons.content_markdown and challenges.instruction began…
Read the full lesson free →Curriculum
7 lessonsThe migration that turned the bilingual columns into JSONB
How a migration converted bilingual string columns to jsonb with jsonb_build_object, and how its down method reverses it.
store_accessor: Rails' own tool for this kind of column
Rails' store_accessor on a jsonb column, tested on a disposable table, including symbol keys returning nil after reload.
Why this app hand-rolled Translatable instead of reaching for store_accessor
Why the app's Translatable concern avoids store_accessor, covering the pt-BR hyphen, name collisions and a dirty tracking cost.
Querying JSONB in SQL: ->>, and one fallback rule written twice
Querying jsonb in SQL with ->> and COALESCE to apply the same locale fallback rule used in Ruby.
GIN indexes: what this app's indexes speed up, and what they don't
What a default GIN index on jsonb accelerates, and how trigram expression indexes replaced the app's original ones.
Schemaless data whose shape the app doesn't control
Using jsonb for data with varying shape: OAuth payloads from providers and an array of localized challenge hints.
Putting it together: one column type, three jobs
A review of the three jobs jsonb does in this app and the files each lesson quoted.