ruby-on-rails - 在Rails中查询Postgres JSON数组字段

我试图查询Postgres数据库中的某个值。我在groups表中有一个名为users的字段，可以用以下任一方式表示：
一

groups: {"data"=>[{"serie"=>5, "year"=>3, "specialization"=>"Matematica", "management_id"=>1, "group_number"=>2}, {"serie"=>5, "year"=>3, "specialization"=>"Matematica", "management_id"=>1, "group_number"=>2}]}

二

groups: [{"serie"=>5, "year"=>3, "specialization"=>"Matematica", "management_id"=>1, "group_number"=>2}, {"serie"=>5, "year"=>3, "specialization"=>"Matematica", "management_id"=>1, "group_number"=>2}]

我对这两种说法都没意见。不过，我似乎无法找到如何获得所有的用户，在第5级，让我们说。我尝试了多个查询，大致如下：

@users = User.where("groups ->> 'data' @>  ?", {serie: 5})
@users = User.where("groups -> 'data' @>  '?'", {serie: 5})
@users = User.where("groups ->> 'data' ->> 'serie' = ?", 5)

还有很多其他的尝试，有些比其他更愚蠢（见上图）。我该怎么做？
我已经确定：

select groups -> 'data' ->> 'serie' from users;
ERROR: cannot extract field from a non-object.

但是，以下查询有效：

select json_array_elements(groups -> 'data') ->> 'serie' from users;

我认为我没有正确地传递列中的数据。我要创建的哈希值是：

pry(#<Overrides::RegistrationsController>)> @response['data']['user']
=> {"last_name"=>"Doe1",
 "first_name"=>"John1",
 "email"=>"[email protected]",
 "groups"=>
  {"data"=>
    [{"serie"=>5, "year"=>3, "specialization"=>"Matematica", "management_id"=>1, "group_number"=>2}, {"serie"=>5, "year"=>3, "specialization"=>"Matematica", "management_id"=>1, "group_number"=>2}]}}

在保存资源之前，如下所示：

pry(#<Overrides::RegistrationsController>)> @resource
=> #<User id: nil, provider: "email", uid: "", first_name: "John1", last_name: "Doe1", email: "[email protected]", role: "Student", created_at: nil, updated_at: nil, groups: {"data"=>[{"serie"=>5, "year"=>3, "specialization"=>"Matematica", "management_id"=>1, "group_number"=>2}, {"serie"=>5, "year"=>3, "specialization"=>"Matematica", "management_id"=>1, "group_number"=>2}]}>

最佳答案

假设：
Postgres 9.4或更高版本。
“获取第5级中的所有用户”应该是指：
“至少有一个数组元素包含{"serie": 5}。可能还有其他人。”
使用第一种较短的数据格式。没有多余的“数据”密钥。
简而言之：使用jsonb而不是json，这样就可以了：

User.where("groups @> ?", '[{"serie": 5}]')

注意方括号，使右侧操作数成为JSON数组。
为什么？
这里最突出的误解是：data type json is not the same as jsonb。
您没有声明实际的表定义，但是您后来对json进行了注释，并且问题中有一个提示：

select json_array_elements(groups -> 'data') ->> 'serie' from users;

json_array_elements()只适用于json，必须是jsonb_array_elements()适用于jsonb。
但是您尝试使用jsonb Operators@>，这对于json是不可能的：

groups -> 'data' @>  '?'

operator ->返回与左侧输入相同的类型。但是@>只为jsonb定义，而不是为json定义。
然后尝试将@>的运算符text用作左侧操作数。也不可能：

groups ->> 'data' @>  ?

对于各种类型（包括Postgres数组），运算符@>都有变体，但对于text和json则没有。
所以，简单的答案是：使用jsonb而不是json。这也允许使用非常高效的索引：
Index for finding an element in a JSON array
json
对于数据类型json可以使用：

SELECT *
FROM   users u
WHERE  EXISTS (
   SELECT 1
   FROM   json_array_elements(u.groups) elem
   WHERE  elem ->> 'serie' = '5'
   );

演示
jsonb：

SELECT *
FROM  (
   VALUES (1, jsonb '[{"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (2,       '[{"serie":7, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":8, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (3,       '[{"serie":9, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
   ) users(id, groups)
WHERE  groups @> '[{"serie": 5}]';

json：

SELECT *
FROM  (
   VALUES (1, json  '[{"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (2,       '[{"serie":7, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":8, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
        , (3,       '[{"serie":9, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}
                    , {"serie":5, "year":3, "specialization":"Matematica", "management_id":1, "group_number":2}]')
   ) users(id, groups)
WHERE  EXISTS (
   SELECT 1
   FROM   json_array_elements(users.groups) elem
   WHERE  elem ->> 'serie'  = '5'
   );

关于ruby-on-rails - 在Rails中查询Postgres JSON数组字段，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/51715603/