在Rails中查询Postgres JSON数组字段

Question

我正在尝试查询Postgres数据库中的某个值。我groups在users表中有一个命名字段，可以用以下两种方式之一表示：

1。

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}]}

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"=>"[e-mail]",
 "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: "[e-mail]", 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}]}>

Answer 1

假设：

• Postgres 9.4或更高版本 。

• “获取所有位于系列5中的用户”的 意思是：
  “具有至少一个包含的数组元素{"serie": 5}。可能还有其他。”
  

• 使用第一个较短的数据格式。没有冗余的“数据”密钥。

简短答案： 使用 jsonb 代替，json这才有效：

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

请注意方括号，以使右侧操作数成为JSON 数组 。

为什么？

此处的主要误解是：数据类型
json 与相同 jsonb
。

您没有声明实际的表定义，但稍后进行了注释json，问题中有一个提示：

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

json_array_elements()仅适用于json，必须jsonb_array_elements()适用jsonb。但是您尝试使用未为定义的
jsonb 运算符@>
json：

groups -> 'data' @>  '?'

所述操作者->将返回相同类型左手输入。但@>仅针对jsonb而不是进行定义json。

然后，您尝试使用运营商@>的text左操作数。 也不可能 ：

groups ->> 'data' @>  ?

运算符@>有多种变体，可以用于各种类型（包括Postgres数组），但不能用于text和不能用于json。

因此，简短的答案：使用jsonb代替json。这也允许使用非常 有效的索引 ：

json

对于数据类型，json您可以使用：

SELECT *
FROM   users u
WHERE  EXISTS (
   SELECT 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 FROM json_array_elements(users.groups) elem 
   WHERE  elem ->> 'serie'  = '5'
   );