Junior: питання на співбесіді з теми «JSON і повнотекстовий пошук»

-- settings = '{"theme": "dark", "notify": {"email": true}, "tags": ["php", "sql"]}'

settings -> 'notify'                 -- {"email": true}   (результат - jsonb)
settings ->> 'theme'                 -- dark              (результат - text)
settings -> 'notify' ->> 'email'     -- true
settings #>> '{notify,email}'        -- true (шлях масивом)
settings -> 'tags' -> 0              -- "php" (елемент масиву за індексом з нуля)

settings @> '{"theme": "dark"}'      -- містить цю пару
settings ? 'theme'                   -- є ключ верхнього рівня
settings ?| array['theme', 'lang']   -- є хоча б один з ключів
settings ?& array['theme', 'lang']   -- є всі ключі

Головна різниця, на якій помиляються: -> повертає jsonb, ->> - text. Для порівняння з рядком чи числом потрібен ->> і приведення типу:

WHERE (data ->> 'price')::numeric > 100
WHERE data ->> 'status' = 'active'

@> (містить) - найкорисніший оператор для фільтрів: його прискорює GIN-індекс, на відміну від порівняння через ->>.

Пастка в PHP: оператори ?, ?|, ?& конфліктують із заповнювачами параметрів PDO - драйвер прийме ? за параметр запиту. Варіанти:

• функції-аналоги: jsonb_exists(settings, 'theme'), jsonb_exists_any(), jsonb_exists_all();
• подвоєний знак ?? (PDO з PHP 7.4 розуміє його як буквальний ?);
• оператор @> там, де він підходить за змістом.

У Laravel запити до JSON-полів пишуть через стрілки в імені колонки - Query Builder перетворює їх на оператори PostgreSQL:

User::where('settings->theme', 'dark')->get();
User::whereJsonContains('settings->tags', 'php')->get();

Докладніше в документації: Оператори jsonb

Значення jsonb - одне ціле: «змінити поле на місці» неможливо, будь-яка зміна створює новий документ. Але PostgreSQL має функції й оператори, які збирають новий документ за вас:

-- встановити чи замінити значення за шляхом
UPDATE users SET settings = jsonb_set(settings, '{notify,email}', 'false')
WHERE id = 1;

-- jsonb_set створює лише останній ключ шляху: якщо об'єкта ui ще немає,
-- документ повернеться без змін. Проміжний об'єкт збирають явно:
UPDATE users SET settings = jsonb_set(
    settings, '{ui}', coalesce(settings -> 'ui', '{}') || '{"sidebar": "collapsed"}'
);

-- злити об'єкти: ключі правого перезапишуть лівий
UPDATE users SET settings = settings || '{"theme": "light", "lang": "uk"}';

-- видалити ключ / шлях / елемент масиву
UPDATE users SET settings = settings - 'beta_features';
UPDATE users SET settings = settings #- '{notify,sms}';

-- вставити в масив
UPDATE users SET settings = jsonb_insert(settings, '{tags,0}', '"laravel"');

Нюанси:

• Значення в jsonb_set - теж JSON: рядок пишуть з подвійними лапками всередині ('"collapsed"'), число й true/false - без.
• jsonb_set з NULL як новим значенням (не JSON-null, а SQL NULL) поверне NULL для всього документа - і стерте налаштування. Для безпечної роботи з NULL є jsonb_set_lax() (PostgreSQL 13+): за замовчуванням він запише JSON-null.
• || зливає лише верхній рівень: вкладений об'єкт праворуч повністю замінить вкладений об'єкт ліворуч, а не зіллється з ним.
• Конкурентні оновлення: два запити, що одночасно змінюють різні ключі одного документа через jsonb_set, не конфліктують - кожен перечитує актуальне значення в UPDATE. А от «прочитати весь JSON у застосунок → змінити → записати назад» дає загублене оновлення.

У Laravel:

$user->update(['settings->notify->email' => false]);   // перетвориться на jsonb_set

Сигнал до зміни схеми: якщо якесь поле JSON постійно оновлюється й фільтрується окремо, - йому, ймовірно, місце в окремій колонці.

Докладніше в документації: Функції обробки JSON