這是大約有 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}]"
我想找到所有具有以下經驗的用戶：
- 開發運營 = 5 年
- 后端開發 = 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 行
uj5u.com熱心網友回復：
如果您使用的是 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)')
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/337134.html
標籤：sql PostgreSQL
上一篇：具有混合型別（Long和字串）的哈希圖時，類java.lang.Long無法轉換為類java.lang.String錯誤