Postgres - How to perform a LIKE query on a JSONB field?

Question

I have a jsonb field called passengers, with the following structure:

note that persons is an array

{
    "adults": {
        "count": 2,
        "persons": [
            {
                "age": 45,
                "name": "Prof. Kyleigh Walsh II",
                "birth_date": "01-01-1975"
            },
            {
                "age": 42,
                "name": "Milford Wiza",
                "birth_date": "02-02-1978"
            }
        ]
    }
}

How may I perform a query against the name field of this JSONB? For example, to select all rows which match the name field Prof?

Here's my rudimentary attempt:

SELECT passengers from opportunities
WHERE 'passengers->adults' != NULL
AND 'passengers->adults->persons->name' LIKE '%Prof';

This returns 0 rows, but as you can see I have one row with the name Prof. Kyleigh Walsh II

Answer 1

This: 'passengers->adults->persons->name' LIKE '%Prof'; checks if the string 'passengers->adults->persons->name' ends with Prof.

Each key for the JSON operator needs to be a separate element, and the column name must not be enclosed in single quotes. So 'passengers->adults->persons->name' needs to be passengers -> 'adults' -> 'persons' -> 'name'

The -> operator returns a jsonb value, you want a text value, so the last operator should be ->>

Also != null does not work, you need to use is not null.

SELECT passengers 
from opportunities
WHERE passengers -> 'adults' is not NULL
 AND  passengers -> 'adults' -> 'persons' ->> 'name' LIKE 'Prof%';

The is not null condition isn't really necessary, because that is implied with the second condition. The second condition could be simplified to:

SELECT passengers 
from opportunities
WHERE passengers #>> '{adults,persons,name}' LIKE 'Prof%';

---

But as persons is an array, the above wouldn't work and you need to use a different approach.

With Postgres 9.6 you will need a sub-query to unnest the array elements (and thus iterate over each one).

SELECT passengers 
from opportunities
WHERE exists (select * 
              from jsonb_array_elements(passengers -> 'adults' -> 'persons') as p(person)
              where p.person ->> 'name' LIKE 'Prof%');

To match a string at the beginning with LIKE, the wildcard needs to be at the end. '%Prof' would match 'Some Prof' but not 'Prof. Kyleigh Walsh II'

---

With Postgres 12, you could use a SQL/JSON Path expression:

SELECT passengers 
from opportunities
WHERE passengers @? '$.adults.persons[*] ? (@.name like_regex "Prof.*")'