jsonb query playground.md

Querying your jsonb column It’s time to use some query operators. PostgreSQL has many others, so read the documentation for a complete list.

Also, always remember to EXPLAIN the queries you’re executing; that way you can adapt your indexes for a better performance.

Users that opt-in newsletter

# preferences->newsletter = true
User.where('preferences @> ?', {newsletter: true}.to_json)

Users that are interested in Ruby

# preferences->interests = ['ruby', 'javascript', 'python']
User.where("preferences -> 'interests' ? :language", language: 'ruby')

This query won’t use the column index; if you need to query arrays, make sure you create an expression index.

CREATE INDEX preferences_interests_on_users ON users USING GIN ((preferences->'interests'))

Users that set Twitter and Github accounts

# preferences->twitter AND preferences->github
User.where('preferences ?& array[:keys]', keys: ['twitter', 'github'])

Users that set Twitter or Github account

# preferences->twitter OR preferences->github
User.where('preferences ?| array[:keys]', keys: ['twitter', 'github'])

Users that live in San Francisco/California

# preferences->state = 'SP' AND preferences->city = 'São Paulo'
User.where('preferences @> ?', {city: 'San Francisco', state: 'CA'}.to_json)