Запросить массив объектов (Postgres с JSONB, Laravel 5)

У меня есть эти данные в поле JSONB:

[
    'first_name' => 'Philipp',
    'last_name' => 'Kühn',
    'size' => 170,
    'hobbies' => [
      'daily' => 'beer',
    ],
    'skills' => [
      'drink beer',
      'drink more beer',
    ],
]

Я новичок в Laravel и Postgres, поэтому хотел выполнить несколько простых запросов.

Вот несколько запросов, которые отлично работают: (json - это имя столбца)

$users = User::whereRaw("json ->> 'first_name' = 'Philipp'")->get();

$users = User::whereRaw("json ->> 'size' > '160'")->get();

$users = User::whereRaw("json #>> '{hobbies, daily}' = 'beer'")->get();

Теперь я хочу проверить, находится ли drink beer в skills, и следующий код не работает:

$users = User::whereRaw("json -> 'skills' ? 'drink beer'")->get();

Здесь у меня синтаксическая ошибка:

SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "$1"
LINE 1: select * from "users" where json ->> 'skills' $1 'drink beer...
^ (SQL: select * from "users" where json ->> 'skills' ? 'drink beer')

Как мне найти drink beer?


postgresql php laravel laravel-5 jsonb
person Philipp Kühn    schedule 27.08.2015    source источник
comment
Ха-хо, похоже, PDO принимает вопросительный знак как параметр запроса… Посмотрите документацию: позиционные параметры: php.net/manual/en/pdostatement.bindparam.php Не знаю, как это обойти.   -  person greg    schedule 27.08.2015
comment
да. это кажется проблемой. Я протестировал необработанный запрос в postico, и все сработало. ах ...   -  person Philipp Kühn    schedule 27.08.2015
comment
как насчет stackoverflow.com/a/16312053/244058?   -  person Florian Klein    schedule 27.08.2015

Ответы (4)


arrow_upward
4
arrow_downward

Как было предложено @tapoueh в твиттере, возможно, можно обойтись путем использования базовой функции оператора: jsonb_exists(jsonb, text). Итак, ваш запрос будет

$users = User::whereRaw("jsonb_exists(json -> 'skills', 'drink beer')")->get();

Добавил @docteur_klein: Ссылка на аналогичную проблему.

person greg    schedule 27.08.2015
comment
отсутствует ), проверьте запрос еще раз - person Pratik; 03.03.2017

arrow_upward
2
arrow_downward

С Laravel 5.6+ поиск в JSON становится действительно простым

https://laravel.com/api/5.6/Illuminate/Database/Query/Builder.html#method_whereJsonContains

User::WhereJsonContains('json->skills', 'drink beer')->get();
person 120DEV    schedule 03.09.2018

arrow_upward
1
arrow_downward

Если вам нужен поиск LIKE, используйте это:

\DB::table('users')
    ->whereRaw("json::json->>'skills' like '%drive a car%'")
    ->get();
person Stas Coder    schedule 12.06.2018

arrow_upward
0
arrow_downward

Если вам нужно найти элемент значения внутри массива объектов, вы можете экранировать такие строки:

$query = "column @> '{\"INDEX_OF_ARRAY\":[{\"INDEX_OF_OBJECT\": \"".$searched_value."\"}]}'";
$colection = YourModel::whereRaw($query);
$colection->get();
person Aline Matos    schedule 03.03.2019