Active Record query array of Json

user_11876504 0
Viewed 840

I have a json column (is an array of objects) in my db containing the many categories related to a product like this:

[
 {
   "id": 1,
   "name": "Category X"
 },
 {
   "id": 2,
   "name": "Category Y"
 },
 ...
]

I need to translate into active record query the following PostgreSQL query:

SELECT * FROM products p, json_array_elements(p.category) as data
WHERE data ->>'name' = 'Sport';

Some of queries that I tried:

sports = Product.where("category ->>'name' = ?", "Sport")

Which returns an empty array (It is wrong since I have records with Sport category in my db)

sports = Product.where("category @> ?", [{name: "Sport"}])

Which raises: TypeError: can't quote Hash

sports = Product.where("category @> ?","{name: 'Sport'}")

Which raises: ERROR: operator does not exist: json @> unknown and the Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.

So I tried:

sports = Product.where("category @> ?", "[{'name': 'Sport'}]")

and

sports = Product.where("category @> ?", "[{'name': 'Sport'}]".to_json)

and some other queries all without success.

These links: PostgreSQL functions-json active record querying didn't help much.

postgresql ruby-on-rails
1 Answers

The reason why you're getting a PG::UndefinedFunction: exception ("operator does not exist: json @> unknown") is because the @> operator is meant to be used in jsonb data type columns, and your products.category column isn't.

For that you can; or to update the data type of the category column, or to explicitly cast the column at the moment of performing the query:

Product.where('category::jsonb @> ?', [{ name: 'Sport' }].to_json)

This:

Product.where("category @> ?", "[{'name': 'Sport'}]")

isn't going to work, since it's not valid syntax.

user_50251160
Related