如何在PostgreSQL的jsonb列中搜索 [英] How to search within a jsonb column in postgresql

问题描述

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

I basically 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"
          }
       },
       "religion":"other",
       "languages":[
          "English",
          "Xen"
       ],
       "last_name":"Eeo",
       "birth_date":"1945-07-28",
       "first_name":"Cee",
       "nationality":"Martian",
       "marital_status":"married"
    }
  }
}

我想提取所有具有火星人"和德兰人"国籍的人.在我的postgresql命令行中，此方法有效

I want to fetch all people who have the "Martian" and "Terran" nationalities.. in my postgresql command line this works

select employees->'personal'->'contact'->'work'->'email' 
from employees 
where employees->'personal' @> '{"nationality":"Martian"}' 
   or employees->'personal' @> '{"nationality":"Terran"}'

这有效..但是很丑..我想运行这样的东西:

this works.. but it's ugly.. i would like to run something like this:

select employees->'personal'->'contact'->'work'->'email' 
from employees 
where employees->'personal'->'nationality' in ('Martian','Terran')

但是我遇到了这样的格式化错误:

but I get formatting errors like this one:

DETAIL:  Token "Martian" is invalid.
CONTEXT:  JSON data, line 1: Martian

推荐答案

您必须使用以文本形式获取值"运算符

You have to use the "get value as text" operator ->> to make this happen:

select employees->'personal'->'contact'->'work'->>'email' 
from employees 
where employees->'personal'->>'nationality' in ('Martian','Terran')

我也将其添加到获取电子邮件中，因为我假设您希望将其作为文本.

I also added it to getting the email since I assume you want it as text.

请注意，转换为文本(employees->'personal'->'nationality')::text将不起作用，因为它不仅返回值，而且还转换了json转换为文本，在这种情况下为"Martian"，包括引号.

Note that casting to text (employees->'personal'->'nationality')::text would not work since it doesn't return just the value but the json converted to text, which in this case is "Martian" including the quotes.

这篇关于如何在PostgreSQL的jsonb列中搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！