Querying an JSONB array of objects in Rails 7

Question

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.

Does this answer your question? [Query on Postgres JSON array field in Rails](https://stackoverflow.com/questions/40702813/query-on-postgres-json-array-field-in-rails) — anothermh, Feb 03 '23 at 16:12
Querying JSON, JSONB and ARRAY columns isn't actually a built in feature in ActiveRecord. You'll have to use a SQL string with the correct syntax for the RDBMS you're using. https://www.postgresql.org/docs/9.5/functions-json.html https://dev.mysql.com/doc/refman/5.7/en/json-function-reference.html https://guides.rubyonrails.org/active_record_postgresql.html — max, Feb 04 '23 at 09:52
PS. The name [`Thead` will clash with the Ruby core class](https://ruby-doc.org/core-2.5.0/Thread.html). Use a different name or define it in a namespace (a module) unless you want to risk some pretty horrenduos bugs. — max, Feb 06 '23 at 09:08

score 1 · Answer 1 · answered Feb 04 '23 at 12:52

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)

You're absolutely right on the last point - the [unnessicary use of JSON/JSONB is a known anti-pattern](https://www.2ndquadrant.com/en/blog/postgresql-anti-patterns-unnecessary-jsonhstore-dynamic-columns/). — max, Feb 06 '23 at 09:04

Querying an JSONB array of objects in Rails 7

1 Answers1