Json的Active Record查询数组

我的数据库中有一个json列(是对象数组),其中包含与此类产品相关的许多类别:

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

我需要将以下PostgreSQL查询转换为活动记录查询:

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

我尝试过的一些查询:

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

这将返回一个空数组(这是错误的,因为我的数据库中有运动类别的记录)

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.

所以我尝试了:

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

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

和其他一些查询都没有成功。

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

评论