如何在jsonb数据中使用postgresql

Question

相关

见这问题

问题

我有一个postgresql表，它有一个类型为jsonb的列。json数据如下所示

{
   "personal":{
      "gender":"male",
      "contact":{
         "home":{
            "email":"ceo@home.me",
            "phone_number":"5551234"
         },
         "work":{
            "email":"ceo@work.id",
            "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或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);

Answer 1

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一词可以省略。对于文献资料

侧向也可以先于从项调用函数，但在这种情况下，它是一个噪声词，因为函数表达式在任何情况下都可以引用前面的项。

另见：在PostgreSQL中横向查询和子查询有什么区别？

语法

from jsonb_array_elements_text(employees->'employment'->'benefits') benefits(benefit)

是一种形式的混叠，每一个文献资料

表别名的另一种形式为表的列以及表本身提供了临时名称： 从table_reference作为别名( column1 [，column2，.])

Answer 2

您可以使用包含运算符?|来检查数组是否包含所需的任何值。

select * from employees 
where employees->'employment'->'benefits' ?| array['Insurance A', 'Insurance B']

如果您希望数组中的所有值都在数组中，那么就需要?&操作符来检查它。