在 Rails 中查询 Postgres JSON 数组字段 [英] Query on Postgres JSON array field in Rails
问题描述
我正在尝试在 Postgres 数据库中查询某个值.我在 users 表中有一个名为 groups 的字段，可以用以下任一方式表示:
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 json_array_elements(groups -> 'data') ->>来自用户的系列"；我认为我没有正确提供列中的数据.我提供的哈希是:
pry(#)>@response['data']['user']=>{姓氏"=>Doe1"，"first_name"="John1","email"="c0f45@example.com",组"=>{数据"=>[{"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(#)>@资源=>#<用户ID:nil，提供者:email"，uid:"，first_name:John1"，last_name:Doe1"，email:c0f45@example.com"，角色:Student"，created_at:nil，updated_at:nil，组:{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 数组.
为什么?
这里的主要误解:数据类型json 与 jsonb 不同.
您没有声明实际的表定义，但您后来评论了 json 并且问题中有一个提示:
select json_array_elements(groups -> 'data') ->>来自用户的系列"；json_array_elements() 仅适用于 json，对于 jsonb 必须是 jsonb_array_elements().但是您尝试使用 jsonb 运算符 @> 未为 json 定义:
groups ->'数据'@>'?运算符 -> 返回与左侧输入相同的类型.但是@>只为jsonb定义，不为json定义.
然后您尝试使用运算符 @> 作为 text 的左侧操作数.也不可能:
groups ->>'数据'@>?对于各种类型(包括 Postgres 数组)，有运算符 @> 的变体，但不适用于 text 和 json>.
所以，简短的回答:使用 jsonb 而不是 json.这也允许使用非常高效的索引:
json
对于数据类型 json，您可以使用:
SELECT *来自用户你哪里存在 (从 json_array_elements(u.groups) 元素中选择WHERE elem ->>'系列' = '5');演示
jsonb:
SELECT *从 (值 (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}]'))用户(ID，组)WHERE 组@>'[{系列":5}]';json:
SELECT *从 (值 (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}]'))用户(ID，组)哪里存在 (从 json_array_elements(users.groups) 元素中选择WHERE elem ->>'系列' = '5');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}]
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?
I have been able to determine that:
select groups -> 'data' ->> 'serie' from users;
ERROR: cannot extract field from a non-object.
However the following query works:
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"=>"c0f45@example.com",
"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: "c0f45@example.com", 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}]}>
Assumptions:
- 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.
Short answer: Use jsonb instead of json and this just works:
User.where("groups @> ?", '[{"serie": 5}]')
Note the square brackets to make the right-hand operand a JSON array.
Why?
The prominent misunderstanding here: data type json is not the same as jsonb.
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() only works for json, would have to be jsonb_array_elements() for jsonb. But you try to use the jsonb operator @> which is not defined for json:
groups -> 'data' @> '?'
The operator -> returns the same type as the left-hand input. But @> is only defined for jsonb, not for json.
Then you try to use the operator @> for text as left-hand operand. Not possible either:
groups ->> 'data' @> ?
There are variants of the operator @> for various types (incl. Postgres arrays), but not for text and not for json.
So, the short answer: Use jsonb instead of json. This allows to use very efficient indexes, too:
json
For data type json you could use:
SELECT *
FROM users u
WHERE EXISTS (
SELECT 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 FROM json_array_elements(users.groups) elem
WHERE elem ->> 'serie' = '5'
);
这篇关于在 Rails 中查询 Postgres JSON 数组字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
