PostgreSQL підтримував JSON до версії 9.1 і будь-яка база банних буде приймати JSON документ як один рядок. Однак, MySQL і PostgreSQL зараз повністю підтримують перевірені дані в форматі JSON швидше у вигляді пар ключ / значення ніж в вигляді одного рядка.
Просто тому що ви можете зберігати JSON.
... не означає що варто
Нормалізація це метод, який використовується для оптимізації баз даних. Правило Першої Нормальною Форми (First Normal Form (1NF)) говорить, що кожна колонка повинна містити одне значення, воно порушене в JSON зберіганням багатозначних значень.
Якщо у вас є чіткі реляційні вимоги до даних, користуйтеся відповідно одиночними значеннями. JSON слід використовувати з обережністю, як крайня міра. Поля значень JSON не можуть бути індексовані, так що уникайте користуватися колонками які все весь час оновлюються або в яких регулярно відбувається пошук. Крім цього, все менше клієнтських додатків підтримують JSON, і технологія оновлюється і стає менш стабільною, в порівнянні з іншими типами.
Кажуть, що хорошим варіантом застосування JSON будуть невеликі за обсягом дані або кастомниє атрибути.
Створення таблиці з даними JSON
1 - таблицю tag. яка зберігає кожне ім'я тега навпаки унікального ID, і
2 - таблицю tagmap в якій є багато-до-багатьох записи ID книг до ID тегом.
Все це буде працювати, але досить громіздко враховуючи простоту завдання. Тому, ми визначимо поле tags JSON в нашій таблиці book в MySQL:
CREATE TABLE `book` (
`Id` mediumint (8) unsigned NOT NULL AUTO_INCREMENT,
`Title` varchar (200) NOT NULL,
`Tags` json DEFAULT NULL,
PRIMARY KEY ( `id`)
) ENGINE
Зауважте, що в колонках JSON не можуть бути дефолтні значення, первинні ключі, до них не можна звертатися за зовнішніми ключам і у них немає індексу. Ви можете створити вторинні індекси в згенерованої віртуально колонці, але можливо простіше буде зберегти значення індексу в окремому полі.
Додавання даних в JSON
Весь документ JSON може бути переданий через оператори INSERT або UPDATE. Наприклад, теги нашої книги можуть бути передані через масив:
JSON також може бути створений за допомогою:
Функції JSON_ARRAY (). яка створює масиви, наприклад
-- повертає [1, 2, "abc"]:
SELECT JSON_ARRAY (1, 2, 'abc');
Функції JSON_OBJECT () яка створює об'єкти
-- повертає:
SELECT JSON_OBJECT ( 'a', 1, 'b', 2);
Функції JSON_MERGE () щоб об'єднувати документи, наприклад
-- повертає [ "a", 1,]:
SELECT JSON_MERGE ( '[ "a", 1]', '');
ви можете кастомизировать будь-яке значення як JSON
(CAST anyValue AS JSON)
Функція JSON_TYPE () дозволяє перевіряти типи значень JSON. Вона повертає OBJECT, ARRAY або помилку, наприклад:
-- повертає ARRAY:
SELECT JSON_TYPE ( '[1, 2, "abc"]');
-- повертає OBJECT:
SELECT JSON_TYPE ( '');
-- повертає помилку:
SELECT JSON_TYPE ( '
Аналогічно, функція JSON_VALID () повертає 1 якщо JSON правильний:
-- повертає 1:
SELECT JSON_TYPE ( '[1, 2, "abc"]');
-- повертає 1:
SELECT JSON_TYPE ( '');
-- повертає 0:
SELECT JSON_TYPE ( 'спроба передати невірний JSON в документ викличе помилку, і вся запису не буде вставлена / оновлена.
Пошук даних JSON
Аналогічно, функція JSON_SEARCH () повертає шлях до знайденого збігу або NULL якщо збігів не виявлено. Вона передається JSON документу, в якому йде пошук, «one» щоб знайти перший збіг, «all» щоб знайти всі збіги, і шукані рядки, наприклад:
-- всі книги з тегами, які починаються з 'Java':
SELECT * FROM `book`
WHERE JSON_SEARCH (tags, 'one', 'Java%') IS NOT NULL;
Шлях в форматі JSON вказує на значення і може бути використаний для отримання або зміни частин документа. Функція JSON_EXTRACT () наочно це показує витягуючи одне або кілька значень.
-- повертає "SitePoint":
SELECT JSON_EXTRACT (
'',
'$ .website'
);
Всі визначення шляху в JSON починаються з $, наступного за іншим селектором.
- точка, за якою слідує ім'я, наприклад $ .website
- [N] де N це позиція в масиві, де індексація йде від нуля
- a. [*] вайлдкарт, яка визначає кількість всіх членів в об'єкті
- a [*] вайлдкарт яка визначає всіх членів в масиві
- prefix ** suffix вайлдкарт, яка визначає всі шляхи які починаються з певного суфікса і закінчуються певним префіксом.
Розглядаючи цей JSON документ:
- $ .a повертає 1
- $ .c повертає [3, 4]
- $ .c [1] повертає 4
- $ .d.e повертає 5
- $ **. E повертає [5]
Виділення шляху JSON в запиті
Ми можемо витягти перший тег мул нашої таблиці book використовуючи запит:
SELECT
name,
tags -> "$ [0]" AS `tag1`
FROM `book`;
Для більш складного прикладу, уявімо що у нас є таблиця user з даними профілю JSON, наприклад:
Ми можемо витягти ім'я твіттера за допомогою JSON шляху:
SELECT
name, profile -> "$. twitter" AS `twitter`
FROM `user`;
SELECT
name, profile -> "$. twitter" AS `twitter`
FROM `user`
WHERE
profile -> "$. twitter" IS NOT NULL;
Внесення змін до JSON
Існують кілька MySQL функцій які можуть вносити зміни в JSON документ при полощи шляхів. До них відносяться:
- JSON_SET (doc, path, val [, path, val].) -
вставляє або оновлює дані в документі - JSON_INSERT (doc, path, val [, path, val].) -
вставляє динячі в документ - JSON_REPLACE (doc, path, val [, path, val].) -
замінює дані в документі - JSON_MERGE (doc, doc [, doc].) -
об'єднує два документа - JSON_ARRAY_APPEND (doc, path, val [, path, val].) -
додає значення в кінець масиву - JSON_ARRAY_INSERT (doc, path, val [, path, val].) -
додає масив в документ - JSON_REMOVE (doc, path [, path].) -
видаляє дані з документа
Мануал по MySQL містить додаткову інформацію про тип даних JSON і пов'язаних з JSON функціях. Знову ж таки, я закликаю вас не використовувати JSON, якщо це не є абсолютно необхідним. Ви можете семуліровать всю документно-орієнтовану NoSQL базу даних в MySQL, але це зведе нанівець багато переваг SQL і ви можете також перейти до реальної системі NoSQL. Як то кажуть, типи даних JSON можуть заощадити сили для більш неясних вимог в додатку на основі SQL.