Query data json in PG with rails

Question

I have a ATable ( id, value ) with value type is json

id | value
--------------------------------------------------
1  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [283, 237, 238] }
2  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [283, 234, 545, 23] }
3  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [98, 56, 2] }
4  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [28345, 100, 333, 987] }

I wrote ATable.where("value->>'user_ids' LIKE ?", "%283%")

Results:

id | value
--------------------------------------------------
1  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [283, 237, 238] }
2  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [283, 234, 545, 23] }
4  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [28345, 100, 333, 987] }

Expecting result:

id | value
--------------------------------------------------
1  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [283, 237, 238] }
2  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [283, 234, 545, 23] }

I try ATable.where("value->>'user_ids' LIKE In (?)", "283") but not working

Thanks.

Answer 1

Not a PG expert but the problem with using "%283%" is that it will match any number containing those digits. That’s why the row with id 4 is returned (28345).

Try the @> operator in conjunction with ->:

ATable.where("value->'user_ids' @> ?", '[283]')

Docs

Not sure this should be tagged as a Rails question, definitely not as a Ruby question.

Answer 2

Perhaps you have a good reason for choosing the PG JSON column type. But since questions like this are asked regularly on SO, let me suggest that the design of your table could be signficantly improved to facilitate your query.

It appears that you are aggregating all the model information into a single column. A much more typical design, for relational databases, and for a Rails app would be a table that has columns start and end. The relationships to the users table would be contained by a reference column within the users table.

So if the table is called preferences, the users table would have a preference_id column. The ActiveRecord models would have associations has_many :users in the Preference model and belongs_to :preference in the User model.

Finally, you can execute your query "the Rails way". If you want to retrieve the preferences of the user with id=283, it's just User.find(283).preferences.

Answer 3

Thanks so much @DennisHackethal, @engineersmnky and @LesNightingill. I just want to find and delete 283. Now, I found the way that delete item without find.

After removed:

id | value
--------------------------------------------------
1  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [237, 238] }
2  | { 'start': '2023-10-02', 'end': '2023-10-05', user_ids: [234, 545, 23] }

But i can run sql like that

def delete_item(item_ids)
  query_where = item_ids.map.with_index { |p, index| "x.el <> :el_#{index}" }.join(" AND ")
  query = <<-SQL 
    UPDATE atables
    SET value = jsonb_set(to_jsonb (value), '{user_ids}', (
      SELECT jsonb_agg(el)
      FROM jsonb_array_elements_text((value ->> 'user_ids')::jsonb) AS x (el)
        WHERE #{query_where}))
  SQL
 
 ActiveRecord::Base.connection.execute(ActiveRecord::Base.send(:sanitize_sql_array, [query, { parse(item_ids) }]))
end

def parse(item_ids)
  item_ids.map.with_index { |p, index| { "el_#{index}".to_sym => p } }.inject(&:merge)
end

Call: 
item_ids = ["283"]
delete_item(item_ids)