This is the dataset with around 200,000 rows
"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}]"
I want to find all the users who's experience is :
- devops = 5years
- backend dev = 5 years
One condition is working in my query:
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
But two conditions together are not working:
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
^^ It's returning 0 rows
CodePudding user response:
If you are on Postgres 12 or later, you can use a JSON path expression:
select *
from users
where jsonb_path_exists(experience, '$[*] ? (@.field == "devops" && @.years > 5)')
and jsonb_path_exists(experience, '$[*] ? (@.field == "backend dev" && @.years > 5)')