2

Related

see this question

Question

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

{
   "personal":{
      "gender":"male",
      "contact":{
         "home":{
            "email":"[email protected]",
            "phone_number":"5551234"
         },
         "work":{
            "email":"[email protected]",
            "phone_number":"5551111"
         }
      },
      ..
      "nationality":"Martian",
      ..
   },
   "employment":{
      "title":"Chief Executive Officer",
      "benefits":[
         "Insurance A",
         "Company Car"
      ],
      ..
   }
}

This query works perfectly well

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

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';

I would like to use any instead like so:

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

but this returns 0 results.. ideas?

What i've also tried

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);
Improve this question
1
  • Would the containment operator ?| help here? Don't have access to a new enough Pgsql to test it out immediately but something like ?| array['Insurance A', 'Insurance B'] Commented Jul 12, 2016 at 10:22

2 Answers 2

Reset to default
4

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')

is equivalent to 

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

The word lateral may be omitted. For 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.

See also: What is the difference between LATERAL and a subquery in PostgreSQL?

The syntax

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

is a form of aliasing, per 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 [, ...]] )

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

questions: 1. why do you put a , right after employees? reference? 2. what's this benefits(benefit).. never seen such a syntax before.. reference?
How to use index on jsonb column?
@morfair - In general, read 8.14.4. jsonb Indexing. Regarding the query in the answer, there is no index that could support jsonb_array_elements().
2

You can use the containment operator ?| to check if the array contains any of the values you want.

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

If you happen to a case where you want all of the values to be in the array, then there's the ?& operator to check for that.

Improve this answer

3 Comments

got this error: ERROR: operator does not exist: text ?| text[] LINE 1: ... employees->'employment'->>'benefits' ?| array['... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. see formatted error here
It's a typo, see my edit. Should be employees->'employment'->'benefits'
@klin Thanks, realized also it should've been with the other operator

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.