CODEWITHSUNDEEP
ruby-on-railsjson
user3576036
user3576036

Reputation: 1425

Querying rails active record based on jsonb column

I have record named User which is having a column called attrs.

The attrs is a jsonb column. The record looks like below:

I am posting as_json record of a User.

{
  "id"=>uuid1,
  "profile_id"=>uuid2,
  "campaign_id"=>uuid3,
  "asset_id"=>uuid4,
  "brand_id"=>"5",
  "region"=>"Non-EU",
  "attrs"=>{
    uuid5=>{
      "label"=>"Email",
      "value"=>"[email protected]"
    },
    uuid6=>{
      "label"=>"Last Name ",
      "value"=>"Yang"
    }
  }
}

I want to query The User active record based on

"label"=>"Email","value"=>"[email protected]".

How can I do this?

Upvotes: 1

Views: 4550

Answers (2)

Martin
Martin

Reputation: 4222

Thanks to a_horse_with_no_name answer here is a raw sql version:

select u.*
from users u
where exists (select *
              from jsonb_each(u.attrs) as t(uid,entry)
              where t.entry ->> 'label' = 'Email'
                and t.entry ->> 'value' = '[email protected]')

And here is AR version:

User.find_by_sql(["
  select u.*
  from users u
  where exists (select *
                from jsonb_each(u.attrs) as t(uid,entry)
                where t.entry ->> 'label' = ?
                  and t.entry ->> 'value' = ?)
  ", 'Email', '[email protected]'])

You can move it to model scope or method. Or to separate query object.

It relies on postgresql jsonb_each method. I just noticed that you did not specify your database

Ref https://stackoverflow.com/a/55512200/4950680

Upvotes: 2

Vishal
Vishal

Reputation: 818

Try,

User.where("settings @> ?", {uuid5: {label: 'Email', value: '[email protected]'}}.to_json)

For more reference see..

https://nandovieira.com/using-postgresql-and-jsonb-with-ruby-on-rails

Upvotes: 5

Related Questions