How to search within a jsonb column in postgresql

Question

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":"ceo@home.me",
             "phone_number":"5551234"
          },
          "work":{
             "email":"ceo@work.id",
             "phone_number":"5551111"
          }
       },
       "religion":"other",
       "languages":[
          "English",
          "Xen"
       ],
       "last_name":"Eeo",
       "birth_date":"1945-07-28",
       "first_name":"Cee",
       "nationality":"Martian",
       "marital_status":"married"
    }
  }
}

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

Answer 1

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.

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.

Answer 2

Use ->> operator:

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