Использование JSON в MySQL

Обратите внимание, что любая база данных будет принимать документы JSON как одностроковый BLOB-объект. Однако MySQL и PostgreSQL поддерживают проверенные данные JSON в реальных парах ключ / значение, а не в базовой строке.

Нормализация — это метод, используемый для оптимизации структуры базы данных. Правило первой нормальной формы (1NF) гласит, что каждый столбец должен содержать одно значение, что явно нарушается при хранении многозначных документов JSON.

Если у вас есть четкие требования к реляционным данным, используйте соответствующие однозначные поля. JSON следует использовать экономно в крайнем случае. Поля значений JSON не могут быть проиндексированы, поэтому не используйте его в столбцах, которые регулярно обновляются или просматриваются. Кроме того, меньше клиентских приложений поддерживают JSON, а технология новее, поэтому она может быть менее стабильной, чем другие типы.

Тем не менее, есть хорошие варианты использования JSON для редко заполненных данных или настраиваемых атрибутов.

Содержание

Создать таблицу с полем JSON
Добавление данных JSON
Поиск данных JSON
Пути JSON
Извлечение путей JSON в запросах
Изменение части документа JSON
Дальнейшая информация

Создать таблицу с полем JSON

Рассмотрим магазин по продаже книг. У всех книг есть идентификатор, ISBN, название, издатель, количество страниц и другие четкие данные о взаимоотношениях. Предположим, вы хотите добавить к каждой книге любое количество тегов категорий. Вы можете добиться этого в SQL, используя:

тег таблица, в которой хранится имя каждого тега с уникальным идентификатором, и
tagmap стол со многими ко многим записям отображения книг идентификаторов в идентификаторы тегов

Это будет работать, но это громоздко и требует значительных усилий для незначительной функции. Следовательно, вы можете определить поле тегов 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=INNODB;

Обратите внимание, что столбцы JSON не могут иметь значение по умолчанию, использоваться в качестве первичного ключа, использоваться в качестве внешнего ключа или иметь индекс. Вы можете создавать вторичные индексы для сгенерированных виртуальных столбцов, но проще и практичнее сохранять значение в отдельном поле, если индексы требуются.

Читайте также: Внешний ключ Postgresql

Добавление данных JSON

Целые документы JSON можно передавать в операторах INSERT или UPDATE. Например, наши книжные теги могут быть переданы в виде массива (внутри строки):

INSERT INTO `book` (`title`, `tags`)
VALUES (
  'ECMAScript 2015: A SitePoint Anthology',
  '["JavaScript", "ES2015", "JSON"]'
);

JSON также можно создать с помощью этих:

Функция JSON_ARRAY (), создающая массивы. Например:

-- returns [1, 2, "abc"]: SELECT JSON_ARRAY(1, 2, 'abc');

Функция JSON_OBJECT (), которая создает объекты. Например:
```
-- returns {"a": 1, "b": 2}:
SELECT JSON_OBJECT('a', 1, 'b', 2);
```
Функция JSON_QUOTE (), которая цитирует строку как значение JSON. Например:
```
-- returns "[1, 2, \"abc\"]":
SELECT JSON_QUOTE('[1, 2, "abc"]');
```
или можно (CAST anyValue AS JSON).

Функция JSON_TYPE () позволяет проверять типы значений JSON. Он должен возвращать OBJECT, ARRAY, скалярный тип (INTEGER, BOOLEAN и т. Д.), NULL или ошибку. Например:

-- returns ARRAY:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns OBJECT:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns an error:
SELECT JSON_TYPE('{"a": 1, "b": 2');

Функция JSON_VALID () возвращает 1, если JSON действителен, или 0 в противном случае:

-- returns 1:
SELECT JSON_TYPE('[1, 2, "abc"]');

-- returns 1:
SELECT JSON_TYPE('{"a": 1, "b": 2}');

-- returns 0:
SELECT JSON_TYPE('{"a": 1, "b": 2');

Попытка вставить недопустимый документ JSON вызовет ошибку, и вся запись не будет вставлена / обновлена.

Поиск данных JSON

Функция JSON_CONTAINS () принимает документ JSON, в котором выполняется поиск, и другой документ для сравнения. Он возвращает 1, когда найдено совпадение. Например:

-- all books with the 'JavaScript' tag:
SELECT * FROM `book` WHERE JSON_CONTAINS(tags, '["JavaScript"]');

Аналогичная функция JSON_SEARCH () возвращает путь к заданному совпадению или NULL, если совпадения нет. Он передал документ JSON, в котором выполняется поиск, ’one’чтобы найти первое совпадение или ’all’найти все совпадения, и строку поиска (где %соответствует любому количеству символов и _соответствует одному символу идентично LIKE). Например:

-- all books with tags starting 'Java':
SELECT * FROM `book` WHERE JSON_SEARCH(tags, 'one', 'Java%') IS NOT NULL;

Пути JSON

Путь JSON нацелен на значения и может использоваться для извлечения или изменения частей документа JSON. Функция JSON_EXTRACT () демонстрирует это, извлекая одно или несколько значений:

-- returns "SitePoint":
SELECT JSON_EXTRACT('{"id": 1, "website": "SitePoint"}', '$.website');

Все определения пути начинаются с символа, $за которым следуют другие селекторы:

точка, за которой следует имя, например $.website
[N] где N — позиция в массиве с нулевым индексом
.[*]подстановочные оценивает все элементы объекта
[*]подстановочные оценивает все элементы массива
в prefix**suffixшаблонные принимает значение всех путей, которые начинаются с префикса имени и в конце с именем суффикс

Следующие примеры относятся к следующему документу JSON:

{
  "a": 1,
  "b": 2,
  "c": [3, 4],
  "d": {
    "e": 5,
    "f": 6
  }
}

Примеры путей:

$.a возвращается 1
$.c возвращается [3, 4]
$.c[1] возвращается 4
$.d.e возвращается 5
$**.e возвращается [5]

Извлечение путей JSON в запросах

Вы можете извлечь имя и первый тег таблицы своей книги, используя запрос:

SELECT
  title, tags->"$[0]" AS `tag1`
FROM `book`;

Для более сложного примера предположим, что у вас есть таблица пользователей с данными профиля JSON. Например:

я бы	имя	профиль
1	Крейг	{«Электронная почта»: [«craig@email1.com», «craig@email2.com»], «twitter»: «@craigbuckler»}
2	SitePoint	{«Электронная почта»: [], «твиттер»: «@sitepointdotcom»}

Вы можете извлечь имя Twitter, используя путь JSON. Например:

SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`;

Вы можете использовать путь JSON в предложении WHERE, чтобы возвращать только пользователей с учетной записью Twitter:

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]…): удаляет данные из документа

Поэтому вы можете добавить «технический» тег к любой книге, в которой уже есть тег «JavaScript»:

UPDATE `book`
  SET tags = JSON_MERGE(tags, '["technical"]')
WHERE
  JSON_SEARCH(tags, 'one', 'JavaScript') IS NOT NULL;

Дальнейшая информация

Руководство MySQL предоставляет дополнительную информацию о типе данных JSON и связанных функциях JSON.

Опять же, я призываю вас не использовать JSON, если в этом нет крайней необходимости. Вы можете эмулировать всю документно-ориентированную базу данных NoSQL в MySQL, но это сведет на нет многие преимущества SQL, и вы также можете переключиться на настоящую систему NoSQL! Тем не менее, типы данных JSON могут сэкономить усилия для более неясных требований к данным в приложении SQL.