Postgres: jsonb对象的多个条件

Question

这个数据集大约有200,000行

"test"  "test"  "[{""field"": ""devops"", ""years"": 8}, {""field"": ""backend dev"", ""years"": 3}]"
"test"  "test"  "[{""field"": ""devops"", ""years"": 9}, {""field"": ""backend dev"", ""years"": 4}]"
"test"  "test"  "[{""field"": ""devops"", ""years"": 9}, {""field"": ""backend dev"", ""years"": 9}]"

我想找到所有用户的经验是:

• devops = +5years
• 后端开发= +5年

一个条件是工作在我的查询中:

with parsed_exp as ( 
select 
"firstName", 
"lastName", 
experience, 
jsonb_array_elements(experience) as e from users )
select * from parsed_exp 
where (e->>'field') = 'backend dev' and (e->>'years')::integer > 5 

但是两个条件同时不起作用:

with parsed_exp as ( 
select 
"firstName", 
"lastName", 
experience, 
jsonb_array_elements(experience) as e from users )
select * from parsed_exp 
where 
(e->>'field') = 'backend dev' and (e->>'years')::integer > 5 and 
(e->>'field') = 'devops' and (e->>'years')::integer > 5

^^返回0行

Answer 1

如果你是Postgres 12或更高版本，你可以使用JSON路径表达式:

select *
from users 
where jsonb_path_exists(experience, '$[*] ? (@.field == "devops" && @.years > 5)')
and jsonb_path_exists(experience, '$[*] ? (@.field == "backend dev" && @.years > 5)')