在Rails中查询Postgres JSON数组字段 [英] Query on Postgres JSON array field in Rails

问题描述

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

I am trying to query a certain value in a Postgres database. I have a field named groups in the users table that can be represented in either of these ways:

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中的所有用户都可以说的.我按照以下方式尝试了多个查询:

I am fine with either of this representations. However I just can't seem to find out how to get all the users that are in serie 5 let's say. I tried multiple queries along the lines of:

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

还有许多其他尝试，有些比其他尝试更愚蠢(请参见上文).我该怎么办?

And many other attempts, some more stupid than others (see above). How would I do it?

我已经能够确定:

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

但是以下查询有效:

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

我认为我没有正确地传递列中的数据.我提供的创建的哈希是:

I think I am not properly delivering the data in the column. The hash I am providing to create is:

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

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

Before saving the resource looks like this:

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

推荐答案

假设:

• Postgres 9.4或更高版本.
• 获取所有在意甲5中的用户" 的意思是:
  具有至少一个包含{"serie": 5}的数组元素.可能还有其他元素."
• 使用第一个较短的数据格式.没有多余的数据"密钥.

• Postgres 9.4 or later.
• "get all the users that are in serie 5" is supposed to mean:
  "with at least one array element that contains {"serie": 5}. There may be others."
• Working with your first, shorter data format. No redundant 'data' key.

简短的答案:使用 jsonb 代替json，这是可行的:

Short answer: Use jsonb instead of json and this just works:

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

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

Note the square brackets to make the right-hand operand a JSON array.

此处的主要误解:数据类型 json 与 jsonb 不同.

The prominent misunderstanding here: data type json is not the same as jsonb.

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

You didn't declare the actual table definition, but you later commented json and there is a hint in the question:

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

json_array_elements()仅适用于json，对于jsonb必须为jsonb_array_elements().
但是您尝试使用 jsonb运算符 @>，对于json 是不可能的:

json_array_elements() only works for json, would have to be jsonb_array_elements() for jsonb.
But you try to use the jsonb Operators @>, which is impossible for json:

groups -> 'data' @>  '?'

运算符-> 返回与左侧输入相同的类型.但是@>仅为jsonb定义，而没有为json定义.

The operator -> returns the same type as the left-hand input. But @> is only defined for jsonb, not for json.

然后，您尝试将text的运算符@>用作左操作数. 也不可能:

Then you try to use the operator @> for text as left-hand operand. Not possible either:

groups ->> 'data' @>  ?

运算符@>有多种类型(包括Postgres数组)，但不是text和json的变体.

There are variants of the operator @> for various types (incl. Postgres arrays), but not for text and not for json.

因此，简短的答案:使用jsonb而不是json.这也允许使用高效索引:

So, the short answer: Use jsonb instead of json. This allows to use very efficient indexes, too:

• 在JSON数组

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

For data type json you could use:

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

Demos

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'
   );

这篇关于在Rails中查询Postgres JSON数组字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！