看到这个问题
我有一个postgresql表,该表具有jsonb类型的列。json数据看起来像这样
{
"personal":{
"gender":"male",
"contact":{
"home":{
"email":"[email protected]",
"phone_number":"5551234"
},
"work":{
"email":"[email protected]",
"phone_number":"5551111"
}
},
..
"nationality":"Martian",
..
},
"employment":{
"title":"Chief Executive Officer",
"benefits":[
"Insurance A",
"Company Car"
],
..
}
}
该查询运行良好
select employees->'personal'->'contact'->'work'->>'email'
from employees
where employees->'personal'->>'nationality' in ('Martian','Terran')
我想获取所有受益于Insurance A
OR类型的员工Insurance B
,这个丑陋的查询有效:
select employees->'personal'->'contact'->'work'->>'email'
from employees
where employees->'employment'->'benefits' ? 'Insurance A'
OR employees->'employment'->'benefits' ? 'Insurance B';
我想使用任何类似的方式:
select * from employees
where employees->'employment'->>'benefits' =
any('{Insurance A, Insurance B}'::text[]);
但这会返回0个结果..想法?
我尝试了以下语法(全部失败):
.. = any({'Insurance A','Insurance B'}::text[]);
.. = any('Insurance A'::text,'Insurance B'::text}::array);
.. = any({'Insurance A'::text,'Insurance B'::text}::array);
.. = any(['Insurance A'::text,'Insurance B'::text]::array);
employees->'employment'->'benefits'
是一个json数组,因此您应取消嵌套以使用其元素进行any
比较。jsonb_array_elements_text()
在横向连接中使用该功能:
select *
from
employees,
jsonb_array_elements_text(employees->'employment'->'benefits') benefits(benefit)
where
benefit = any('{Insurance A, Insurance B}'::text[]);
语法
from
employees,
jsonb_array_elements_text(employees->'employment'->'benefits')
相当于
from
employees,
lateral jsonb_array_elements_text(employees->'employment'->'benefits')
该词lateral
可以省略。对于文档:
LATERAL也可以在函数调用FROM项之前,但是在这种情况下,它是一个干扰词,因为在任何情况下,函数表达式都可以引用更早的FROM项。
另请参见:LATERAL和PostgreSQL中的子查询之间有什么区别?
语法
from jsonb_array_elements_text(employees->'employment'->'benefits') benefits(benefit)
根据文档,是别名的一种形式
表别名的另一种形式为表的列以及表本身赋予临时名称:
FROM table_reference [AS]别名(column1 [,column2 [,...]])
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句