Блог

У меня есть поле jsonb с именем passengers , со следующей структурой:

обратите внимание, что persons — это массив

 {
    "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"
            }
        ]
    }
}
  

Как я могу выполнить запрос к полю name этого JSONB? Например, чтобы выбрать все строки, соответствующие полю name Prof ?

Вот моя элементарная попытка:

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

Это возвращает 0 строк, но, как вы можете видеть, у меня есть одна строка с именем Prof. Kyleigh Walsh II

Это: 'passengers->adults->persons->name' LIKE '%Prof'; проверяет, заканчивается ли строка 'passengers->adults->persons->name' на Prof .

Каждый ключ для оператора JSON должен быть отдельным элементом, а имя столбца не должно быть заключено в одинарные кавычки. Так 'passengers->adults->persons->name' должно быть passengers -> 'adults' -> 'persons' -> 'name'

-> Оператор возвращает jsonb значение, вам нужно text значение, поэтому последним оператором должно быть ->>

Также != null не работает, вам нужно использовать is not null .

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

is not null Условие на самом деле не обязательно, потому что это подразумевается вторым условием. Второе условие может быть упрощено до:

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

Но поскольку persons это массив, вышеуказанное не сработает, и вам нужно использовать другой подход.

С Postgres 9.6 вам понадобится подзапрос, чтобы отменить определение элементов массива (и, таким образом, выполнить итерацию по каждому из них).

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

Чтобы сопоставить строку в начале с ПОДОБНЫМ, подстановочный знак должен быть в конце. '%Prof' будет соответствовать 'Some Prof' , но не 'Prof. Kyleigh Walsh II'

С Postgres 12 вы могли бы использовать выражение SQL / JSON Path:

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