postgresql - 如何将 postgresql any 与 jsonb 数据一起使用

Question

相关

参见 this问题

问题

我有一个 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';

我想使用 any而是像这样:

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()在 lateral join :

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 可以省略。对于 the documentation :

LATERAL can also precede a function-call FROM item, but in this case it is a noise word, because the function expression can refer to earlier FROM items in any case.

另请参阅:What is the difference between LATERAL and a subquery in PostgreSQL?

语法

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

是别名的一种形式，根据 the documentation

Another form of table aliasing gives temporary names to the columns of the table, as well as the table itself:

FROM table_reference [AS] alias ( column1 [, column2 [, ...]] )