0

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"=>"guillaume@example.com"},
   {"name"=>"Fabien", "email"=>"fabien@example.com"},]

id: 2,
 subject: "World",
 participants:
  [{"name"=>"guillaume", "email"=>"guillaume@example.com"},
   {"name"=>"hakim", "email"=>"hakim@example.com"},]

participants is a JSONB array column I want to find records who have the email "hakim@example.com" in participants column.

Guillaume
  • 1,437
  • 2
  • 15
  • 17
  • 1
    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

1 Answers1

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' = 'fabien@example.com'
)

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, 'fabien@example.com')

(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)

Pascal
  • 8,464
  • 1
  • 20
  • 31
  • 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