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 — anothermh
– anothermh, Commented Feb 3, 2023 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. postgresql.org/docs/9.5/functions-json.html dev.mysql.com/doc/refman/5.7/en/json-function-reference.html guides.rubyonrails.org/active_record_postgresql.html — max
– max, Commented Feb 4, 2023 at 9:52
PS. The name Thead will clash with the Ruby core class. Use a different name or define it in a namespace (a module) unless you want to risk some pretty horrenduos bugs. — max
– max, Commented Feb 6, 2023 at 9:08

Pascal · Accepted Answer · 2023-02-04 12:52:11Z

1

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)

answered Feb 4, 2023 at 12:52

Pascal

8,6861 gold badge24 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

max Over a year ago

You're absolutely right on the last point - the unnessicary use of JSON/JSONB is a known anti-pattern.

Collectives™ on Stack Overflow

Querying an JSONB array of objects in Rails 7

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related