Rails on JSONB 'where' multiple search terms in nested array of objects

Question

I have in Rails a jsonb column on table profile with the following structure

add_column :profile, :properties, :jsonb, default: {}

user.build_profile(
  properties: {
    languages: [
        {name: "German", level: 3}, 
        {name: "Russian", level: 2},
        {name: "English", level: 3}
    ],
    skills: ['Alpha', 'Beta', 'Gamma']
  }
)

user.build_profile(
  properties: {
    languages: [
        {name: "German", level: 2}, 
        {name: "Russian", level: 3},
        {name: "Spanish", level: 3}
    ],
    skills: ['Alpha','Beta']
  }
)


user.build_profile(
  properties: {
    languages: [
        {name: "Italian", level: 2}, 
        {name: "Spanish", level: 3}
    ],
    skills: ['Gamma']
  }
)

I am able to retrieve one Language like so

@profiles.where("properties @> ?", {'languages': [{'name':'German'}] }.to_json)

and multiple skills (x OR y) like so

@profiles.where("properties -> 'skills' ?| array[:skills]", skills: ['Alpha', 'Beta'])

BUT I need to retrieve MULTIPLE languages query term German and Russian

How can I achieve?

Thanks in advance

Wai Ha Lee · Accepted Answer · 2020-12-28 21:54:28Z

1

Not familiar with JSONB but ActiveRecord does support an and operator like

@profiles.where("properties @> ?", {'languages': [{'name':'German'}] }.to_json).
  and(@profiles.where("properties @> ?", {'languages': [{'name':'Russian'}] }.to_json))

https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-and

edited Dec 28, 2020 at 21:54

Wai Ha Lee

8,856102 gold badges61 silver badges100 bronze badges

answered Dec 28, 2020 at 13:12

Christian Bruckmayer

2,2178 silver badges12 bronze badges

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

1 Comment

Gael Sapori Over a year ago

I have consider this option before made the question, but since none was able to found out an other elegeant way, i ll pick your answer. thanks

Collectives™ on Stack Overflow

Rails on JSONB 'where' multiple search terms in nested array of objects

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related