如何将PostgreSQL与JSONB数据一起使用 [英] How to use postgresql any with jsonb data

问题描述

请参见此问题

我有一个postgresql表，该表的列类型为jsonb. json数据看起来像这样

I have a postgresql table that has a column of type jsonb. the json data looks like this

{
   "personal":{
      "gender":"male",
      "contact":{
         "home":{
            "email":"[e-mail]",
            "phone_number":"5551234"
         },
         "work":{
            "email":"[e-mail]",
            "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的员工，此丑陋查询有效:

I would like to fetch all employees who have benefits of type Insurance A OR Insurance B, this ugly query works:

 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条结果..想法吗?

but this returns 0 results.. ideas?

我尝试了以下语法(均失败):

I tried the following syntaxes (all failed):

.. = 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() https://www.postgresql.org/docs/current/static/queries-table-expressions.html"rel =" nofollow noreferrer>横向加入:

employees->'employment'->'benefits' is a json array, so you should unnest it to use its elements in any comparison. Use the function jsonb_array_elements_text() in lateral join:

select *
from 
    employees, 
    jsonb_array_elements_text(employees->'employment'->'benefits') benefits(benefit)
where
    benefit = any('{Insurance A, Insurance B}'::text[]);

---

语法

---

The syntax

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

等效于

from 
    employees, 
    lateral jsonb_array_elements_text(employees->'employment'->'benefits')

可以省略单词lateral.对于文档:

LATERAL也可以在函数调用FROM项之前，但是在这种情况下 这是一个干扰词，因为函数表达式可以引用 在任何情况下都是较早的FROM项目.

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.

另请参见: LATERAL和PostgreSQL中的子查询之间有什么区别?

语法

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

是文档

表别名的另一种形式将临时名称赋予 表格以及表格本身:

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

FROM table_reference [AS]别名(column1 [，column2 [，...]])

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

这篇关于如何将PostgreSQL与JSONB数据一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！