Як працювати з JSON у MySQL: оператори -> і ->> та JSON_TABLE?

Витягти значення з JSON-колонки:

SELECT id,
       settings->'$.theme'   AS theme_json,   -- "dark" (JSON-значення з лапками)
       settings->>'$.theme'  AS theme         -- dark (звичайний рядок)
FROM users;

Різниця важлива в порівняннях: settings->'$.theme' = 'dark' порівнює JSON з рядком і може дати не той результат, якого очікуєте; для умов і сортування зазвичай потрібен ->>.

Шляхи: $.address.city, елемент масиву $.tags[0], усі елементи $.tags[*].

JSON_TABLE - перетворити JSON-масив на рядки й колонки, з якими працює звичайний SQL:

SELECT o.id, items.sku, items.qty
FROM orders o,
     JSON_TABLE(o.payload, '$.items[*]' COLUMNS (
         sku VARCHAR(32) PATH '$.sku',
         qty INT         PATH '$.qty' DEFAULT '1' ON EMPTY
     )) AS items
WHERE items.sku = 'A-1';

Корисно, щоб розібрати масив позицій із зовнішнього API, порахувати агрегати по елементах масиву чи перенести дані з JSON у нормальні таблиці під час міграції.

Зміна JSON без перезапису всього документа:

UPDATE users SET settings = JSON_SET(settings, '$.theme', 'light') WHERE id = 7;

Для JSON_SET, JSON_REPLACE, JSON_REMOVE InnoDB може оновити документ частково, і в бінарний журнал (з binlog_row_value_options=PARTIAL_JSON) потрапить лише зміна.

У Laravel:

User::where('settings->theme', 'dark')->get();          // json_unquote(json_extract(...)), тобто те саме, що ->>
User::whereJsonContains('settings->tags', 'php')->get();
$user->update(['settings->theme' => 'light']);          // JSON_SET

Обмеження: умова по settings->>'$.theme' не використовує звичайних індексів. Для частих фільтрів потрібна згенерована колонка з індексом або функціональний індекс; для масивів - багатозначний індекс (MEMBER OF). Якщо поле фільтрують у кожному запиті, це сигнал винести його в окрему колонку.

Докладніше в документації: MySQL: функції JSON_TABLE