在 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屋!