У MySQL нет возможности напрямую индексировать документы JSON, но есть альтернатива: генерируемые столбцы.


С момента введения поддержки типа данных JSON в MySQL 5.7.8 не хватает одной вещи: способности индексировать значения JSON. Для того, чтобы обойти это ограничение, можно использовать генерируемые столбцы. Эта возможность, представленная в MySQL 5.7.5, позволяет разработчикам создавать столбцы, содержащие информацию, полученную из других столбцов, предопределенных выражений или вычислений. Генерируя столбец из значений JSON, а затем индексируя его, можно практически индексировать поле с JSON.


Набор данных в формате JSON, используемый в данной статье, можно скачать на Гитхабе. Он содержит список игроков со следующими элементами: идентификатор игрока, его имя и игры, в которые он играл (Battlefield, Crazy Tennis и Puzzler).


{
    "id":1,
    "name":"Sally",
    "games_played":{
        "Battlefield":{
            "weapon":"sniper rifle",
            "rank":"Sergeant V",
            "level":20
        },
        "Crazy Tennis":{
            "won":4,
            "lost":1
        },
        "Puzzler":{
            "time":7
        }
    }
},
…

Поле Battlefield содержит любимое оружие игрока, его текущий ранг и уровень этого ранга. Crazy Tennis включает в себя количество выигранных и проигранных игр, а Puzzler содержит время, затраченное игроком на прохождение игры. Создадим начальную таблицу:


CREATE TABLE `players` (  
    `id` INT UNSIGNED NOT NULL,
    `player_and_games` JSON NOT NULL,
    PRIMARY KEY (`id`)
);

Этот запрос создает таблицу players, состоящую из идентификатора и JSON-данных, а также устанавливает в поле id первичный ключ.


Нужно построить индекс по полю с JSON. Давайте посмотрим, что нужно добавить в команду CREATE TABLE.


Генерация столбцов


Для создания генерируемых столбцов в операторе CREATE TABLE используется следующий синтаксис:


`column_name` datatype GENERATED ALWAYS AS (expression)

Ключевыми словами здесь являются GENERATED ALWAYS и AS. Фраза GENERATED ALWAYS необязательна. Она необходима только в том случае, если вы хотите явно указать, что этот столбец таблицы — генерируемый. Необходимо, чтобы слово AS сопровождалось выражением, которое вернет значение для генерируемого столбца.


Начнем с этого:


`names_virtual` VARCHAR(20) GENERATED ALWAYS AS ...

Cоздаем столбец с именем names_virtual длиной до 20 символов, в котором будем хранить значение поля «name» из объекта JSON. Обращаться к полю «name» в JSON будем с использованием MySQL-оператора ->>, который эквивалентен написанию JSON_UNQUOTE (JSON_EXTRACT (...)). Эта конструкция вернет значение поля «name» из объекта JSON в качестве результата.


`names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name')

Этот код означает, что мы берём поле c JSON player_and_games и извлекаем значение из JSON по ключу «name» — дочернее по отношению к корню.


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


[VIRTUAL|STORED] [UNIQUE [KEY]] [[NOT] NULL] [[PRIMARY] KEY]

Уникальные для генерируемых столбцов ключевые слова VIRTUAL и STORED указывают на то, будут ли значения сохраняться в таблице.


Ключевое слово VIRTUAL используется по умолчанию. Оно означает, что значения столбца не сохраняются и не занимают место для хранения. Они вычисляются при каждом чтении строки. Если вы создаете индекс с виртуальным столбцом, значение всё же сохраняется — в индексе.


Ключевое слово STORED указывает, что значения вычисляются при записи данных в таблицу: при вставке или обновлении. В этом случае индексу не нужно сохранять значение.


Другие параметры — необязательные ограничения, которые гарантируют, что значения поля будут NULL или NOT NULL, а также добавления ограничений на индекс, например, UNIQUE или PRIMARY KEY. Для гарантии существования значения следует использовать NOT NULL при создании столбца, однако ограничения зависят от варианта использования. В примере будет использоваться NOT NULL, так как у игроков обязательно есть имя.


Запрос, создающий таблицу:


CREATE TABLE `players` (  
   `id` INT UNSIGNED NOT NULL,
   `player_and_games` JSON NOT NULL,
   `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL, 
   PRIMARY KEY (`id`)
);

Заполнение таблицы тестовыми данными:


INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{  
    "id": 1,  
    "name": "Sally",
    "games_played":{    
       "Battlefield": {
          "weapon": "sniper rifle",
          "rank": "Sergeant V",
          "level": 20
        },                                                                                                                          
       "Crazy Tennis": {
          "won": 4,
          "lost": 1
        },  
       "Puzzler": {
          "time": 7
        }
      }
   }'
);
...

Содержимое таблицы players на Гисте или…


С поехавшим форматированием
SELECT * FROM `players`;

+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
| id | player_and_games                                                                                                                                                                                           | names_virtual |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+
|  1 | {"id": 1, "name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Sergeant V", "level": 20, "weapon": "sniper rifle"}, "Crazy Tennis": {"won": 4, "lost": 1}}}                  | Sally         |
|  2 | {"id": 2, "name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": {"rank": "Major General VIII", "level": 127, "weapon": "carbine"}, "Crazy Tennis": {"won": 10, "lost": 30}}}            | Thom          |
|  3 | {"id": 3, "name": "Ali", "games_played": {"Puzzler": {"time": 12}, "Battlefield": {"rank": "First Sergeant II", "level": 37, "weapon": "machine gun"}, "Crazy Tennis": {"won": 30, "lost": 21}}}           | Ali           |
|  4 | {"id": 4, "name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": {"rank": "Chief Warrant Officer Five III", "level": 73, "weapon": "pistol"}, "Crazy Tennis": {"won": 47, "lost": 2}}} | Alfred        |
|  5 | {"id": 5, "name": "Phil", "games_played": {"Puzzler": {"time": 7}, "Battlefield": {"rank": "Lt. Colonel III", "level": 98, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 130, "lost": 75}}}          | Phil          |
|  6 | {"id": 6, "name": "Henry", "games_played": {"Puzzler": {"time": 17}, "Battlefield": {"rank": "Captain II", "level": 87, "weapon": "assault rifle"}, "Crazy Tennis": {"won": 68, "lost": 149}}}             | Henry         |
+----+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------+

Таблица включает столбец names_virtual, в который вставлены все имена игроков. Структура таблицы players:


SHOW COLUMNS FROM `players`;

+------------------+------------------+------+-----+---------+-------------------+
| Field            | Type             | Null | Key | Default | Extra             |
+------------------+------------------+------+-----+---------+-------------------+
| id               | int(10) unsigned | NO   | PRI | NULL    |                   |
| player_and_games | json             | NO   |     | NULL    |                   |
| names_virtual    | varchar(20)      | NO   |     | NULL    | VIRTUAL GENERATED |
+------------------+------------------+------+-----+---------+-------------------+

Поскольку мы не указали, является ли генерируемый столбец VIRTUAL или STORED, по умолчанию MySQL автоматически сделал столбец VIRTUAL. Чтобы проверить, являются ли столбцы VIRTUAL или STORED, просто запустите вышеуказанный запрос SHOW COLUMNS, и он покажет либо VIRTUAL GENERATED, либо STORED GENERATED.


Теперь, когда мы настроили таблицу и виртуальный столбец, добавим еще четыре столбца, используя операции ALTER TABLE и ADD COLUMN. Они будут содержать уровни Battlefield, выигранные и проигранные игры в теннис и время в Puzzler.


ALTER TABLE `players` ADD COLUMN `battlefield_level_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Battlefield.level') NOT NULL AFTER `names_virtual`;  
ALTER TABLE `players` ADD COLUMN `tennis_won_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".won') NOT NULL AFTER `battlefield_level_virtual`;  
ALTER TABLE `players` ADD COLUMN `tennis_lost_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".lost') NOT NULL AFTER `tennis_won_virtual`;  
ALTER TABLE `players` ADD COLUMN `times_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Puzzler.time') NOT NULL AFTER `tennis_lost_virtual`;  

Опять же, запустив запрос SHOW COLUMNS FROM players;, мы видим, что рядом с ними все столбцы указаны как VIRTUAL GENERATED. Это означает, что мы успешно настроили новые созданные VIRTUAL столбцы.


Код Гисте или…


С поехавшим форматированием
+---------------------------+------------------+------+-----+---------+-------------------+
| Field                     | Type             | Null | Key | Default | Extra             |
+---------------------------+------------------+------+-----+---------+-------------------+
| id                        | int(10) unsigned | NO   | PRI | NULL    |                   |
| player_and_games          | json             | NO   |     | NULL    |                   |
| names_virtual             | varchar(20)      | NO   |     | NULL    | VIRTUAL GENERATED |
| battlefield_level_virtual | int(11)          | NO   |     | NULL    | VIRTUAL GENERATED |
| tennis_won_virtual        | int(11)          | NO   |     | NULL    | VIRTUAL GENERATED |
| tennis_lost_virtual       | int(11)          | NO   |     | NULL    | VIRTUAL GENERATED |
| times_virtual             | int(11)          | NO   |     | NULL    | VIRTUAL GENERATED |
+---------------------------+------------------+------+-----+---------+-------------------+

Выполнение запроса SELECT показывает нам все значения из VIRTUAL COLUMNS, которые должны выглядеть так:


Код Гисте или…


С поехавшим форматированием
SELECT `names_virtual`, `battlefield_level_virtual`, `tennis_won_virtual`, `tennis_lost_virtual`, `times_virtual` FROM `players`;

+---------------+---------------------------+--------------------+---------------------+---------------+
| names_virtual | battlefield_level_virtual | tennis_won_virtual | tennis_lost_virtual | times_virtual |
+---------------+---------------------------+--------------------+---------------------+---------------+
| Sally         |                        20 |                  4 |                   1 |             7 |
| Thom          |                       127 |                 10 |                  30 |            25 |
| Ali           |                        37 |                 30 |                  21 |            12 |
| Alfred        |                        73 |                 47 |                   2 |            10 |
| Phil          |                        98 |                130 |                  75 |             7 |
| Henry         |                        87 |                 68 |                 149 |            17 |
+---------------+---------------------------+--------------------+---------------------+---------------+

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


Индексирование генерируемых столбцов


При установке вторичных индексов на значения генерируемых столбцов VIRTUAL значения сохраняются в индексе. Это дает преимущества: размер таблицы не увеличивается, появляется возможность использования индексов в MySQL.


Давайте сделаем простой запрос к генерируемому столбцу, чтобы увидеть, как он выглядит, прежде чем индексировать его. Изучив детали запроса при выборе names_virtual и имени «Sally», получим следующее:


EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ALL
possible_keys: NULL  
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
     filtered: 16.67
        Extra: Using where

Для этого запроса MySQL просматривает каждую строку, чтобы найти «Sally». Однако, можно получить совершенно другой результат, добавив индекс к столбцу:


CREATE INDEX `names_idx` ON `players`(`names_virtual`);  

Теперь, выполняя тот же запрос, получаем:


EXPLAIN SELECT * FROM `players` WHERE `names_virtual` = "Sally"\G  
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: ref
possible_keys: names_idx  
          key: names_idx
      key_len: 22
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL

Как видно, индекс в столбце ускорил запрос, просматривая только одну строку вместо шести, используя индекс names_idx. Давайте создадим индексы для остальных виртуальных столбцов, следуя тому же синтаксису, что и names_idx:


CREATE INDEX `times_idx` ON `players`(`times_virtual`);  
CREATE INDEX `won_idx` ON `players`(`tennis_won_virtual`);  
CREATE INDEX `lost_idx` ON `players`(`tennis_lost_virtual`);  
CREATE INDEX `level_idx` ON `players`(`battlefield_level_virtual`);  

Можно проверить, были ли проиндексированы все наши столбцы, запустив:
Код Гисте или…


С поехавшим форматированием
SHOW INDEX ON `players`;

+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name               | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| players |          0 | PRIMARY   |            1 | id                        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | names_idx |            1 | names_virtual             | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | times_idx |            1 | times_virtual             | A         |           5 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | won_idx   |            1 | tennis_won_virtual        | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | lost_idx  |            1 | tennis_lost_virtual       | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
| players |          1 | level_idx |            1 | battlefield_level_virtual | A         |           6 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------+--------------+---------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Теперь, когда созданы несколько индексов в генерируемых столбцах, давайте усложним поиск. В этом примере выбираются идентификаторы, имена, выигранные теннисные игры, уровень Battlefield и время Puzzler для игроков, которые имеют уровень выше 50, а также выигравших 50 теннисных игр. Все результаты будут упорядочены по возрастанию в соответствии с временем в Puzzler. Команда SQL и результаты будут выглядеть так:


SELECT `id`, `names_virtual`, `tennis_won_virtual`, `battlefield_level_virtual`, `times_virtual` FROM `players` WHERE (`battlefield_level_virtual` > 50 AND  `tennis_won_virtual` > 50) ORDER BY `times_virtual` ASC;

+----+---------------+--------------------+---------------------------+---------------+
| id | names_virtual | tennis_won_virtual | battlefield_level_virtual | times_virtual |
+----+---------------+--------------------+---------------------------+---------------+
|  5 | Phil          |                130 |                        98 |             7 |
|  6 | Henry         |                 68 |                        87 |            17 |
+----+---------------+--------------------+---------------------------+---------------+

Давайте посмотрим, как MySQL выполнял этот запрос:


*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: players
   partitions: NULL
         type: range
possible_keys: won_idx,level_idx  
          key: won_idx
      key_len: 4
          ref: NULL
         rows: 2
     filtered: 66.67
        Extra: Using where; Using filesort

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


Тем не менее остается один вопрос: для чего нужны STORED генерируемые столбцц? Как их использовать и как они работают?


Хранение значений в генерируемых столбцах


Использование ключевого слова STORED при настройке генерируемого столбца обычно не предпочтительно, поскольку в основном значения в таблице сохраняются дважды: поле с JSON и в STORED столбце. Тем не менее, существует три сценария, когда в MySQL нужно использовать столбец STORED:


  1. индексирование первичных ключей,
  2. полнотекстовый индекс/индекс R-tree,
  3. столбец, который часто выбирается.

Синтаксис добавления генерируемого STORED столбца, совпадает с созданием генерируемых столбцов VIRTUAL, за исключением того, что нужно добавить ключевое слово STORED:


`id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED NOT NULL,

Чтобы посмотреть как использовать STORED, создадим еще одну таблицу. Она будет брать id из данных JSON и хранить его в STORED столбце. Установим PRIMARY KEY для столбца id:


CREATE TABLE `players_two` (  
    `id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED NOT NULL,
    `player_and_games` JSON NOT NULL,
    `names_virtual` VARCHAR(20) GENERATED ALWAYS AS (`player_and_games` ->> '$.name') NOT NULL,
    `times_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Puzzler.time') NOT NULL,
    `tennis_won_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".won') NOT NULL,
    `tennis_lost_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played."Crazy Tennis".lost') NOT NULL,
    `battlefield_level_virtual` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.games_played.Battlefield.level') NOT NULL, 
    PRIMARY KEY (`id`),
    INDEX `times_index` (`times_virtual`),
    INDEX `names_index` (`names_virtual`),
    INDEX `won_index` (`tennis_won_virtual`),
    INDEX `lost_index` (`tennis_lost_virtual`),
    INDEX `level_index` (`battlefield_level_virtual`)
);

Добавим тот же набор данных в player_two, за исключением того, что удалим id, который ранее добавили в операцию INSERT:


INSERT INTO `players_two` (`player_and_games`) VALUES ('{  
    "id": 1,  
    "name": "Sally",  
    "games_played":{    
...
);

После того, как данные были вставлены в таблицу, запустим SHOW COLUMNS в новой таблице, чтобы узнать, как MySQL создал столбцы. Обратите внимание, что поле id теперь — STORED GENERATED и содержит индекс PRIMARY KEY.


SHOW COLUMNS FROM `players_two`;

+---------------------------+-------------+------+-----+---------+-------------------+
| Field                     | Type        | Null | Key | Default | Extra             |
+---------------------------+-------------+------+-----+---------+-------------------+
| id                        | int(11)     | NO   | PRI | NULL    | STORED GENERATED  |
| player_and_games          | json        | NO   |     | NULL    |                   |
| names_virtual             | varchar(20) | NO   | MUL | NULL    | VIRTUAL GENERATED |
| times_virtual             | int(11)     | NO   | MUL | NULL    | VIRTUAL GENERATED |
| tennis_won_virtual        | int(11)     | NO   | MUL | NULL    | VIRTUAL GENERATED |
| tennis_lost_virtual       | int(11)     | NO   | MUL | NULL    | VIRTUAL GENERATED |
| battlefield_level_virtual | int(11)     | NO   | MUL | NULL    | VIRTUAL GENERATED |
+---------------------------+-------------+------+-----+---------+-------------------+

Замечание об использовании PRIMARY KEY с генерируемыми столбцами: MySQL не позволит создавать первичные ключи для генерируемых VIRTUAL столбцов. На самом деле, если не указать STORED в поле id, MySQL выдает следующую ошибку:


ERROR 3106 (HY000): 'Defining a virtual generated column as primary key' is not supported for generated columns.  

В то же время, если не устанавливать индекс первичного ключа и попытаться вставить данные, MySQL выдает сообщение об ошибке:


ERROR 3098 (HY000): The table does not comply with the requirements by an external plugin.  

Это означает, что у таблицы нет первичного ключа. Поэтому нужно вернуться и пересоздать таблицу, либо удалить столбец id и добавить генерируемый STORED столбец с первичным ключом, например:


ALTER TABLE `players_two` ADD COLUMN `id` INT GENERATED ALWAYS AS (`player_and_games` ->> '$.id') STORED PRIMARY KEY;  

Вывод


В статье показано как эффективно хранить данные JSON в MySQL, а так же как создавать индексы благодаря генерируемым столбцам. Использование генерируемых столбцов позволит размещать индексы по определенным элементам данных JSON. Именно эта гибкость делает MySQL очень привлекательной для использования JSON.

Комментарии (9)


  1. Zeitung
    12.02.2018 02:06

    Не могли бы Вы дать ссылку на статью в которой хотя-бы базово и на пальцах рассматриваются преимущества хранения\использования JSON в БД вместо того чтоб распарсить его в коде приложения? Может я не там смотрю (или нужно попробовать DuckDuckGo), но гугл выдаёт статьи о том как использовать JSON, но не могу найти конретики о выхлопе такого подхода.
    Интересуюсь на будущее, т.к. текущий проект на древнейшей DB2, с которой нужно спрыгивать.


    1. win0err Автор
      12.02.2018 02:12

      Вкратце: это удобно. Отдал JSON, а у тебя всё по полочкам разложилось.
      Почему делать на стороне СУБД — а зачем писать логику на стороне приложения, если можно её реализовать парой команд на стороне СУБД? Меньше кода — меньше ошибок.
      Об этом был доклад Коли Самохвалова на Хайлоаде (слайды).


      1. Zeitung
        12.02.2018 04:33

        Спасибо, обязательно почитаю.
        «по полочкам разложилось» — но в нормальном коде обычно так-же происходит. Т.е. в приведенном в статье примере вы задаёте «маппинг» json->column на SQL, в Java->Spring->Jackson будет DTO класс с аннотациями, который пишеться в базу\отдельные колонки. Конечно если искать по JSON дереву стандартным StreamAPI будет не очень красиво, но есть приличные библиотеки.


      1. Zeitung
        12.02.2018 06:05

        Посмотрел доклад, спасибо, было познавательно. Я абсолютно согласен с тем что при возможности на уровень БД нужно выносить (а лучше дублировать) часть валидации и необходимой логики. С другой стороны — мне не приходилось работать с кодом, который бы адекватно транслировал ошибки проверок при манипуировании строками в БД. Но конкретно с JSON… Даже если брать пример докладчика: «пришёл менеджер и сказал что ему нужен доступ к базе данные пофиксить (грубо PHPMyAdmin)», то ему же с JSON придёться бороться? Хотя мне никогда не приходилось работать с системами в которых несколько приложения работают с одной БД (обычно разный UI, но Бекенд\API\DataModel+validation общий)…


    1. yushkevichv
      12.02.2018 02:23

      Если совсем на пальцах — хранение json позволяет использовать произвольные структуры данных. Это преимущество не реляционных БД, которое стало возможным использовать и в MySql, весьма добавляет гибкости.
      Что касается использования типа данных json в БД (относительно, например, сохранения json в поле с типом text) — есть ряд задач, когда требуется выполнять поиск по json, например.
      Из документации Laravel например (https://laravel.com/docs/5.5/queries#json-where-clauses)

      $users = DB::table('users')
                      ->where('options->language', 'en')
                      ->get();
      
      $users = DB::table('users')
                      ->where('preferences->dining->meal', 'salad')
                      ->get();
      


      Это «выгоднее» отдать на уровень БД.
      Код из Laravel привёл для более наглядного примера.


      1. Zeitung
        12.02.2018 04:28

        Спасибо. «Произвольные структуры данных» — Вы имеете в виду случаи когда JSON содержит «мусорную» информацию (лишние\случайные ноды)? Ведь если «meal» находиться в разных уровнях вложенности — ничего найдено не будет? Из приведенного примера — я всё-равно пишу путь к интересующему участку… И если брать любую библиотеку для работы с JSON — всё будет примерно также. Хотя в большинстве случаев там будет последовательный перебор массивов (кажется, на Java была либа которая удачно распараллеливала поиск по XPath->XML). Хотя, наверное, на уровне БД должны использовать более «умные» методы поиска?


  1. musicriffstudio
    12.02.2018 09:28

    JSON это иерархическая структура (дерево). MySQL, как и любая БД прекрасно работает с любыми, в том числе и древовидными структурами. Именно для этого реляционная алгебра и создавалась.

    Перевод данных из одного вида (текст JSON) в другой (нормализованная структура БД) и обратно это крошечная часть по сравнению с обработкой самих данных.

    Обработка данных это не получение куска по иду, для такого достаточно разложить файлики в отдельной папочке. Обработка это, скажем, выбрать всех пользователей с процентом выигрышей за последние 10 дней больше 70% и увеличить им уровень на единицу — вполне реалистичная для игровых серверов.


  1. ahdenchik
    12.02.2018 09:54

    От адепта PG ремарка: к «генерации столбцов» нужна ещё «генерация хранимых процедур» и прочие вытекающие из возможности такого рода генерации штуки.


  1. oxidmod
    12.02.2018 10:21

    Хранить JSON стоит только тогда, когда искать по его полях не нужно. Если нужно искать по произвольных полях, да еще не по одному — никакие генерируемые колонки не помогут. Лучше тогда уж поставить эластик.
    А лучше вообще не хранить в мускуле JSON (пока-что по крайней мере)