Find Records Where A Jsonb Column Contains A Specific Key In Rails
Settings bags, API payloads, and feature-flag hashes all end up in a jsonb column, and eventually you need to query them — every account with a particular preference set, every webhook whose payload has a given field.
Description
PostgreSQL gives you three operators worth knowing. ? tests for the presence of a top-level key. @> tests containment — does the document include this fragment. ->> extracts a value as text for comparison.
? collides with ActiveRecord’s bind placeholder, so it must be escaped as ?? inside a where string. This is the detail that sends people in circles.
A GIN index supports ? and @> across the whole document. A B-tree index on a single extracted expression is smaller and faster when one key is hot.
Sample input:
Account.where("settings ?? :key", key: "beta_features")
Sample Output:
SELECT accounts.* FROM accounts WHERE settings ? 'beta_features'
Answer
# Key exists at the top level — note the escaped ??
Account.where("settings ?? :key", key: "beta_features")
# Any of these keys exists
Account.where("settings ??| array[:keys]", keys: %w[beta_features early_access])
# All of these keys exist
Account.where("settings ??& array[:keys]", keys: %w[theme timezone])
# Containment — key present AND equal to a value (uses a GIN index)
Account.where("settings @> ?", { theme: "dark" }.to_json)
# Rails 7.1+ shorthand for containment
Account.where(settings: { theme: "dark" })
# Extract as text and compare
Account.where("settings ->> 'theme' = ?", "dark")
# Nested path
Account.where("settings #>> '{notifications,email}' = ?", "true")
# Key is absent or null
Account.where("NOT (settings ?? 'theme')")
# Indexes
# add_index :accounts, :settings, using: :gin
# add_index :accounts, "(settings ->> 'theme')", name: "idx_accounts_theme"
Check viewARU - Brand Newsletter!
Newsletter to DEVs by DEVs - boost your Personal Brand & career! 🚀
