Как использовать поля данных JSON в базах данных MySQL

В моей статье « SQL против NoSQL: различия» я упоминал, что грань между базами данных SQL и NoSQL становится все более размытой, поскольку каждый лагерь перенимает функции другого. MySQL 5.7 Базы данных InnoDB и PostgreSQL 9.4 обе напрямую поддерживают типы документов JSON в одном поле. В этой статье мы рассмотрим реализацию JSON MySQL более подробно.

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

Просто потому, что вы можете хранить JSON …

… это не следует за вами.

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

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

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

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

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

1. таблица тегов, в которой каждое имя тега хранится по уникальному идентификатору
2. таблица тегов с записями « многие ко многим», отображающая идентификаторы книг в идентификаторы тегов

Это будет работать, но это громоздкие и значительные усилия для незначительной функции. Поэтому мы определим поле JSON тегов в таблице книг нашей базы данных 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 не могут иметь значение по умолчанию, использоваться в качестве первичного ключа, ссылаться на внешний ключ или иметь индекс. Вы можете создавать вторичные индексы для сгенерированных виртуальных столбцов , но, возможно, проще сохранить индексированное значение в отдельном поле.

Добавление данных 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_MERGE () функция для объединения документов, например

   -- returns ["a", 1, {"key": "value"}]:
  SELECT JSON_MERGE('["a", 1]', '{"key": "value"}');

• или вы можете (CAST anyValue AS JSON)

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

 -- 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 действителен:

 -- 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 вызовет ошибку, и вся запись не будет вставлена ​​/ обновлена.

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

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

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

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

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

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

 SELECT
  name, profile->"$.twitter" AS `twitter`
FROM `user`
WHERE
  profile->"$.twitter" IS NOT NULL;

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