Робота з JSON даними в СУБД Mysql 5.7

22

Від автора: створюючи різні проекти, досить часто виникає задача, збереження деякої інформації в базу даних. Але погодьтеся – це досить буденна операція для будь-якого розробника. Але що якщо необхідно зберегти в одну з таблиць масив або об’єкт — звичайно в явному вигляді, дані типи даних зберегти не можливо, але якщо привести їх до формату JSON, збереження реалізується дуже просто, так як JSON це звичайна рядок.

Робота з JSON даними в СУБД Mysql 5.7Робота з JSON даними в СУБД Mysql 5.7

При цьому якщо – це рядок, то використовувати її для побудови різних SQLзапросов не представляється можливим, особливо якщо необхідно вибрати значення деякого властивості.

Але з виходом системи управління базами даних MySQL версії 5.7, все значно змінилося, так як розробники додали новий тип даних полів, створюваних таблиць, під назвою MySQL JSON. Кажучи тим самим, що був доданий функціонал завдяки якому можна працювати з інформацією, що зберігається в рядку формату JSON і використовувати її для побудови SQLзапросов. Тому в даному уроці я хотів показати, як працювати з JSON даними, використовуючи СУБД MySQL.

Для початку, хотів би зазначити, що підтримка формату JSON введена в систему управління базами даних MySQL, починаючи з версії 5.7, а значить для гарного розуміння даного уроку і тестування результатів роботи, Вам необхідно перевірити версію використовуваної СУБД MySQL і при необхідності виконати оновлення. Якщо Ви використовуєте програмне забезпечення OpenServer, як інструмент для веб-розробки, то зазначена вище, версія баз даних, що входить в комплект версії 5.2.2 пакету OpenServer.

Тепер давайте створимо нову базу даних під назвою test, яку ми будемо використовувати для тестування доступних функцій. Після цього відкриємо консоль продукту OpenServer і з’єднаємося з сервером бази даних, використовуючи команду:

mysql –u root -p

При цьому після символів -u через пробіл вказується ім’я користувача, а після -р пароль.

Робота з JSON даними в СУБД Mysql 5.7

Далі, Вам запропонують повторити пароль для вказаного користувача. Але так як за замовчуванням користувач root, зареєстрований без пароля, означає просто, пропускаємо цей крок, натискаючи клавішу Enter.

Робота з JSON даними в СУБД Mysql 5.7

Після цього ми побачимо повідомлення, що з’єднання із сервером MySQL успішно виконано.

Робота з JSON даними в СУБД Mysql 5.7

Далі, створимо таблицю articles, яку ми будемо використовувати для додавання даних. При цьому так само використовуємо консоль, а значить, першим ділом виберемо базу даних для роботи, використовуючи команду.

use test;

Робота з JSON даними в СУБД Mysql 5.7

Потім виконаємо SQL запит для створення нової таблиці.

CREATE TABLE `articles` (
`id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL,
`keys` json DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB

Зверніть увагу, що створюється поле ‘keys’, даний тип якого визначений як json. По суті, в цьому полі буде зберігатися звичайна рядок даних JSON, але використовуючи спеціальні функції, Ви отримаєте можливість використовувати це поле для формування різних умов отримання даних.

Отже, таблиця створена, а значить, давайте додамо кілька записів. При цьому консоль нам вже не потрібно, так як подальше кодування ми будемо вести, з допомогою мови PHP. Для цього виконаємо з’єднання з раніше створеної бази даних.

connect_error) {
die(‘Помилка підключення (‘ . $mysqli->connect_errno . ‘)’
. $mysqli->connect_error);
}

Далі створимо масив, який після кодування в рядок JSON, збережемо в базу даних в полі ‘keys’ таблиці ‘articles’.

$array = [
‘one’,
‘two’,
‘three’,
‘four’,
‘five’
];

Потім безпосередньо записуємо інформацію в базу даних.

$data = json_encode($array);
$sql = «INSERT INTO `articles` (`title`,`keys`) VALUES (
‘Article two’,
‘».$data.»‘
)»;

Далі, аналогічно запишемо об’єкт у полі ‘keys’.

class Test {
public $one = 1;
public $two = 2;
public $three = 3;
public $four = 4;
public $five = 5;
}
$obj = new Test;
$data = json_encode($obj);
$sql = «INSERT INTO `articles` (`title`,`keys`) VALUES (
‘Article two’,
‘».$data.»‘
)»;

І останній запис – більш складний багато-вимірний масив.

class Test {
public $one = 1;
public $two = 2;
public $three = 3;
public $four = 4;
public $five = array(‘value1′,’value2’);
}
$obj = new Test;
$array = [
‘one’,
‘key’=>’value’,
$obj,
[‘six’,’seven’,’eight’],
‘five’
];
$data = json_encode($array);
$sql = «INSERT INTO `articles` (`title`,`keys`) VALUES (
‘Article two’,
‘».$data.»‘
)»;

Звичайно, збереження інформації у базу даних в полі формату JSON здійснюється після декодування масиву або об’єкта в рядок. Тобто, по суті, аналогічним чином ми можемо зберегти дану рядок у полі довільного типу. Але тільки поле формату JSON надає можливість використовувати дані зберігаються в рядку JSON для формування різноманітних умов в SQL-запитах.

Тепер давайте подивимося, як ми можемо використовувати дані формату JSON. Для початку проведемо вибірку записів з таблиці «articles», в якій у полі «keys» розташовується простий індексний масив формату JSON в одній з комірок якого збережено значення «one»:

$sql = «SELECT * FROM `articles` WHERE JSON_CONTAINS(`keys`, ‘[\»one\»]’)»;
$result = $mysqli->query($sql);
foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) {
echo $row[‘title’] .’ | ‘.$row[‘keys’].’
‘;
}

При цьому на екрані браузера ми побачимо наступний результат.

Робота з JSON даними в СУБД Mysql 5.7

Функція JSON_CONTAINS() – повертає істину, якщо в полі, ім’я якого передається в якості першого параметра, міститься значення вказане в якості другого аргументу. Тепер, використовуючи цю функцію, виберемо записи, у яких в полі «keys» розташовується об’єкт формату JSON, з певною властивістю.

$sql = «SELECT * FROM `articles` WHERE JSON_CONTAINS(`keys`, ‘{\»one\»:1}’)»;
$result = $mysqli->query($sql);
foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) {
echo $row[‘title’] .’ | ‘.$row[‘keys’].’
‘;
}

Далі розглянемо функцію JSON_EXTRACT(), яка повертає одне значення рядка формату JSON.

$sql = «SELECT JSON_EXTRACT((SELECT `keys` FROM `articles` WHERE id=’1′),’$[0]’) as `title`»;
$result = $mysqli->query($sql);
foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) {
echo $row[‘title’];
}

При цьому функція JSON_EXTRACT(), в якості першого аргументу приймає рядок JSON, в якій буде здійснюватися пошук елемента, зазначеного в першому параметрі функції. Зверніть увагу, що перший аргумент формується з допомогою вкладеного запиту типу SELECT. У другому рядку Ви вказуєте у вигляді шаблону, які дані необхідно вибрати із рядка першого аргументу. Відповідно у шаблоні Ви можете використовувати наступні елементи:

$[] – мітка позначає індексний масив;

$[x] – являє комірку масиву з індексом х;

$[*] – представляє довільну комірку масиву.

$. – мітка, що представляє об’єкт.

$.* — являє собою довільне властивість об’єкта.

Робота з JSON даними в СУБД Mysql 5.7

У наступному прикладі давайте отримаємо значення під індексом 1, властивості five, яке належить об’єкту, розташованого в довільній комірці масиву формату JSON.

$sql = «SELECT JSON_EXTRACT((SELECT `keys` FROM `articles` WHERE `id` = 4),’$.*.five[1]’) as title»;
$result = $mysqli->query($sql);
foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) {
echo $row[‘title’];
}

Робота з JSON даними в СУБД Mysql 5.7

Так само дані зберігаються в рядку формату JSON, Ви можете використовувати для формування SQL запитів, без використання функцій.

$sql = «SELECT `title`,`keys`->’$.*.five’ AS `key` FROM `articles`»;
$result = $mysqli->query($sql);
foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) {
echo $row[‘title’]. ‘| ‘. $row[‘key’]. ‘
‘;
}

Зверніть увагу, що звернення до значень рядка JSON, здійснюється з допомогою знака ->. Тобто вказуємо ім’я поля, в якому розташовується рядок JSON, потім спеціальний оператор -> і нарешті, шаблон для пошуку необхідного значення.

Робота з JSON даними в СУБД Mysql 5.7

Крім цього, Ви можете використовувати значення поля формату JSON, для формування умови WHERE при створенні запиту SQL.

$sql = «SELECT `title`,`keys` AS `key` FROM `articles` WHERE `keys`->’$.*.five[1]’ IS NOT NULL»;
$result = $mysqli->query($sql);
foreach ($result->fetch_all(MYSQLI_ASSOC) as $row) {
echo $row[‘title’]. ‘| ‘. $row[‘key’]. ‘
‘;
}

Робота з JSON даними в СУБД Mysql 5.7

Тобто за аналогією з попереднім прикладом для пошуку необхідного значення JSON, використовується шаблон. На цьому даний урок завершено. Всього Вам доброго і вдалого кодування!!!