In a Rails 8.1 app on PostgreSQL, how do you add a jsonb column for sneaker details and find sneakers by one key inside it?
answer
- t.jsonb with default: {}
- Ruby Hash with string keys
- SQL fragment in where with ->>
- @> containment for a GIN index
- in-place edits are detected
basics
~20 sAdd it with t.jsonb :details, null: false, default: {} and Active Record hands you a Ruby Hash with string keys. Query a key with a SQL fragment such as where("details ->> 'brand' = ?", "Nike"); where(details: {...}) compares the whole document.
solid answer
~40 sIn the migration, `t.jsonb :details, null: false, default: {}` (or `add_column :sneakers, :details, :jsonb`). The PostgreSQL adapter maps the column to a JSON type, so `sneaker.details` is a Ruby `Hash`; assignment round-trips through JSON, so keys come back as **strings** even if you assigned symbols, and `details[:brand]` is `nil`. To filter on one key, write SQL: `Sneaker.where("details ->> 'brand' = ?", "Nike")`, keeping the value as a bind. Plain hash conditions do not reach inside: `where(details: { brand: "Nike" })` compares the entire document for equality. For speed, `add_index :sneakers, :details, using: :gin` serves containment queries such as `where("details @> ?", { brand: "Nike" }.to_json)`. Changing the hash in place (`details["colorway"] = "Bred"`) is detected as a change, so `save` writes it.
code
ruby · 9 linessneaker = Sneaker.new(details: { brand: "Nike", release_year: 1985 })
sneaker.details # => {"brand"=>"Nike", "release_year"=>1985}
sneaker.details[:brand] # => nil (keys are strings)
sneaker.save!
sneaker.details["colorway"] = "Bred"
sneaker.changed? # => true, detected in place
Sneaker.where("details ->> 'brand' = ?", "Nike")go deeper
Recall t.jsonb with default: {}, that you get a Hash with string keys, and the where fragment with ->> and a bind.
Explain the JSON type's round-trip cast, in-place change detection, and why a hash condition compares the whole document.
Choose GIN for containment or an expression index for one hot key, and promote keys that every query needs to columns.
Set the team rule for what goes in jsonb versus typed columns, weighing flexibility against constraints and query cost.
## Declaring the column A sneaker marketplace has a few attributes every listing shares (name, price, seller) and many that vary by brand and model: colorway, release year, materials, collaboration name. A `jsonb` column holds the varying part without a migration for every new key: ```ruby class AddDetailsToSneakers < ActiveRecord::Migration[8.1] def change add_column :sneakers, :details, :jsonb, null: false, default: {} add_index :sneakers, :details, using: :gin end end ``` Inside `create_table` the same column is `t.jsonb :details, null: false, default: {}`. The `null: false, default: {}` pair matters in Ruby: code can call `sneaker.details["brand"]` on any record without guarding against `nil`. ## What Active Record gives you back The PostgreSQL adapter maps `jsonb` to a JSON attribute type, which: - **deserialises** the stored document into Ruby objects: a JSON object becomes a `Hash`, arrays become `Array`, numbers become `Integer` or `Float`; - **casts on assignment by serialising and parsing again**, so `sneaker.details = { brand: "Nike" }` is immediately `{"brand" => "Nike"}`. Symbol keys do not survive, and `details[:brand]` returns `nil`; - **tracks in-place changes**: the type compares the stored document with the current Hash, so `sneaker.details["colorway"] = "Bred"` followed by `save` writes the change without any extra call. Values that are not JSON types are converted on the way in: a `Date` becomes a string and stays a string when read back. ## Querying one key Active Record has no hash syntax for reaching inside a JSON document, so the condition is a SQL fragment with bind parameters: | Need | Rails code | |---|---| | sneakers of one brand | `Sneaker.where("details ->> 'brand' = ?", "Nike")` | | a numeric comparison | `Sneaker.where("(details ->> 'release_year')::int >= ?", 2020)` | | documents containing a sub-document | `Sneaker.where("details @> ?", { brand: "Nike" }.to_json)` | | sort by a key | `Sneaker.order(Arel.sql("details ->> 'brand'"))` | The `?` placeholders keep user input out of the SQL text. Wrapping a raw ordering expression in `Arel.sql` tells Active Record the fragment is trusted, so it must never contain user input. What these operators return and how they compare is PostgreSQL's own subject; for a Rails answer, the point is that the key name and the operator live in the SQL string and the value lives in a bind. ## The hash-condition trap ```ruby Sneaker.where(details: { brand: "Nike" }) ``` reads naturally but does not mean "brand is Nike". Because `details` is a real column, Active Record does not treat the nested hash as an association; it builds a plain equality, `details = '{"brand":"Nike"}'`, which matches only documents whose **whole** content is exactly that object. A listing that also has a colorway does not match. ## Indexes that actually help - `add_index :sneakers, :details, using: :gin` builds a **GIN** index on the whole document. It serves containment (`@>`) and key-existence queries, which is why the containment form above is the one to prefer for filters. - A `->>` equality on one key is not served by that GIN index. If one key is filtered constantly, add an expression index for it, `add_index :sneakers, "(details ->> 'brand')", name: "index_sneakers_on_details_brand"`, or promote the key to a real column. ## Writing changes without losing them - **`save` writes the whole document.** Active Record sends the full Hash in the `UPDATE`, so two requests that edit different keys of the same listing at the same time can overwrite each other: the last save wins. - **Atomic key updates** need SQL that changes one key inside the database, run through `update_all` with bound values, for example `Sneaker.where(id: id).update_all(["details = jsonb_set(details, '{colorway}', to_jsonb(?::text))", "Bred"])`. It skips validations and callbacks, which is the price of atomicity. - **Validations** on nested keys are hand-written: a `validate` method that checks `details["release_year"]` is an Integer, for example. ## When jsonb is the wrong choice 1. **Keys every row has and every query filters on** (price, seller, status) belong in typed columns with constraints and plain indexes. 2. **Data you validate field by field** is easier as columns; model validations on nested keys are hand-written. 3. **Relationships** belong in tables with foreign keys, not ids buried in JSON. Interviewers like a candidate who reaches for `jsonb` for the sparse, varying attributes and keeps the rest relational.
- Why does sneaker.details[:brand] return nil right after assigning { brand: "Nike" } to a jsonb attribute in Rails 8.1?The JSON attribute type casts an assigned value by serialising it to JSON and parsing it back, so the Hash is rebuilt with string keys before you read it. `details["brand"]` works; for symbol-style access, wrap reads yourself or declare accessors with `store_accessor`, which reads string keys for you.
- When would you add an expression index instead of relying on a GIN index over the whole jsonb column?When one key is filtered with `->>` equality or sorted constantly. The whole-column GIN index serves containment and key-existence queries, not `->>` comparisons, so `add_index :sneakers, "(details ->> 'brand')"` gives that one lookup a btree. If many queries need the key, it is probably a real column.
- Expects symbol keys to survive assignment to a jsonb attribute
- Writes where(details: { brand: "Nike" }) to filter on one key
- Interpolates request params straight into the ->> SQL fragment
- Calls details_will_change! before every in-place jsonb edit
- Moves every column into jsonb to avoid writing migrations