Project

rails_psql_jsonb

0.0
The project is in a healthy, maintained state
rails_psql_jsonbbubiche/rails_psql_jsonbHomepageDocumentationSource CodeBug TrackerWiki
Provides jsonb_where, jsonb_order, and atomic update methods (jsonb_update!, jsonb_delete_key, jsonb_array_append, jsonb_increment, and more) for PostgreSQL JSONB columns in Rails ActiveRecord. Generates safe, type-cast SQL with no lost-update race conditions under concurrency.
2005
2006
2007
2008
2009
2010
2011
2012
2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
 Popularity
Downloads
83
Stars
0
Forks
0
Watchers
1
 Releases
Current version
0.2.0
Total releases
1
First release
2026-04-06
Latest release
2026-04-06
 Development
Primary Language
Ruby
Licenses
MIT
Average date of last 50 commits
2025-03-05
Reverse Dependencies
0

 Dependencies

Development

database_cleaner-active_record
~> 2.1
rake
~> 13.0
rspec
~> 3.0

Runtime

activerecord
>= 6.1
activesupport
>= 6.1
pg
>= 1.5.6
 Project Readme

RailsPsqlJsonb

Helpers for querying and atomically updating PostgreSQL JSONB columns in Rails ActiveRecord.

Inspired by https://github.com/madeintandem/jsonb_accessor and https://github.com/antoinemacia/atomic_json

Installation

Add to your Gemfile:

gem "rails_psql_jsonb"

Include in your model:

class Friend < ApplicationRecord
  include RailsPsqlJsonb
end

Usage

Querying

Contains / equality / numeric comparison

# @> contains
Friend.jsonb_where(column_name: "props", operator: "contains", value: { age: 90 })

# Numeric operators: gt, lt, gte, lte, eq  (also accept >, <, >=, <=, =)
Friend.jsonb_where(column_name: "props", json_keys: ["age"], operator: "gt", value: 20)

# Nested key path
Friend.jsonb_where(column_name: "props", json_keys: ["address", "city"], operator: "eq", value: "Berlin")

# Exclusion
Friend.jsonb_where_not(column_name: "props", operator: "contains", value: { active: true })

Key existence

# Records where the key exists
Friend.jsonb_where_exists(column_name: "props", key: "age")

# Records where the key is absent
Friend.jsonb_where_exists(column_name: "props", key: "age", exclude: true)

# Records having any of the given keys
Friend.jsonb_where_exists_any(column_name: "props", keys: ["age", "score"])

# Records having all of the given keys
Friend.jsonb_where_exists_all(column_name: "props", keys: ["age", "name"])

# Scope check to a nested object
Friend.jsonb_where_exists(column_name: "props", key: "city", json_keys: ["address"])

Ordering

Friend.all.jsonb_order(column_name: "props", json_keys: ["age"], direction: "desc")
# NULLs sort first for desc, last for asc

Updating

All update methods are atomic at the database level using jsonb_set.

Set / merge keys

# Merges new keys without overwriting unrelated keys
friend.jsonb_update!({ "props" => { "age" => 31 } })
friend.jsonb_update!({ "props" => { "age" => 31, "score" => 100 } })

# Without touching updated_at
friend.jsonb_update_columns({ "props" => { "age" => 31 } })

# Without running validations
friend.jsonb_update({ "props" => { "age" => 31 } })

Delete a key

friend.jsonb_delete_key("props", "age")                    # removes props['age']
friend.jsonb_delete_key("props", "address", "city")        # removes props['address']['city']
friend.jsonb_delete_key_columns("props", "age")            # no updated_at touch

Array operations

# Append — initializes to [value] if the key doesn't exist
friend.jsonb_array_append("props", ["tags"], "ruby")

# Remove all occurrences — returns [] if the last element is removed
friend.jsonb_array_remove("props", ["tags"], "ruby")

Numeric increment / decrement

friend.jsonb_increment("props", ["score"])        # +1 (default)
friend.jsonb_increment("props", ["score"], 5)     # +5
friend.jsonb_increment("props", ["score"], -1)    # -1 (decrement)
# Missing key is initialized to 0 before applying the delta

Batch update

# Wraps multiple jsonb_update! calls in a single transaction
Friend.jsonb_batch_update([
  [friend_a, { "props" => { "score" => 10 } }],
  [friend_b, { "props" => { "score" => 20 } }],
])

GIN index recommendation

For best query performance, create a GIN index on your JSONB column. Use jsonb_gin_index_sql to get the correct SQL for your migration:

# In a migration:
execute Friend.jsonb_gin_index_sql(column_name: "props")
# => CREATE INDEX ON "friends" USING GIN ("props" jsonb_path_ops);

# jsonb_path_ops  — smaller index, faster for @> (contains) queries (default)
# jsonb_ops       — also supports ?, ?|, ?& key-existence operators
execute Friend.jsonb_gin_index_sql(column_name: "props", using: :jsonb_ops)

Development

To run tests you must have PostgreSQL installed and create the test database:

PGPASSWORD=postgres createdb -U postgres -h localhost rails_psql_jsonb_test

Tests assume PostgreSQL is running on localhost:5432 with username postgres and password postgres.

After checking out the repo, run bin/setup to install dependencies. Then run rake spec to run the tests. You can also run bin/console for an interactive prompt.

To release a new version, update version.rb, then run bundle exec rake release to create a git tag, push commits and the tag, and push the .gem file to rubygems.org.

License

The gem is available as open source under the terms of the MIT License.