CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordjsonb
Guillaume
Guillaume

Reputation: 1537

Querying an JSONB array of objects in Rails 7

I'm on Ruby On Rails 7 I have a class ActiveRecord class called Thread.

There are 2 records :

 id: 1,
 subject: "Hello",
 participants:
  [{"name"=>"guillaume", "email"=>"[email protected]"},
   {"name"=>"Fabien", "email"=>"[email protected]"},]

id: 2,
 subject: "World",
 participants:
  [{"name"=>"guillaume", "email"=>"[email protected]"},
   {"name"=>"hakim", "email"=>"[email protected]"},]

participants is a JSONB array column I want to find records who have the email "[email protected]" in participants column.

Upvotes: 0

Views: 714

Answers (1)

Pascal
Pascal

Reputation: 8637

I don't think this is supported directly by AR.

You can "unwrap" the jsonb array using _jsonb_array_elements" or as the docs say: "Expands the top-level JSON array into a set of JSON values."

See https://www.postgresql.org/docs/current/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE

You can query the DB with SQL like this:

select * from threads
where exists(
    select true 
    from jsonb_array_elements(participants) as users 
    where users->>'email' = '[email protected]'
)

Which translates into something like this in AR

condition = <<~SQL
  exists(
    select true 
    from jsonb_array_elements(participants) as users
    where users->>'email' = ?
  )
SQL

Thread.where(condition, '[email protected]')

(from the top of my head. can not test this)

IMHO: This is something that I see often happening with JSONB data: looks nice and simple in the beginning and then turns out to be more complicated (to query, validate, enforce integrity, insert) than a proper relationship (e.g. a participants table that links users/threads)

Upvotes: 1

Related Questions