Простое использование полей типа MySQL JSON

MySQL

Поля типа данных JSON поддерживаются, начиная с MySQL 5.7. JSON в настоящее время является самой популярной формой взаимодействия с данными, и MySQL продолжает следовать за ним.В версии 5.7 был добавлен тип данных JSON. Хотя сейчас должно быть относительно немного приложений, это может не стать тенденцией. Давайте кратко узнаем об операциях MySQL с типами данных JSON и некоторых встроенных функциях (следующее содержимое основано на MySQL 8.0.13).

PS: Следующее содержание неразумно, например, при написании SQL ключевое слово должно быть с большой буквы, SELECT * отключен и т.д., просто для интуиции, уж простите~

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

create table test_json ( 
    `id` int auto_increment,
    `obj_json` JSON,
    `arr_json` JSON,
    primary key (`id`)
)engine = InnoDB default charset = utf8mb4;

В полях JSON не нужно устанавливать длину, а также нельзя устанавливать значение по умолчанию.

Вставить JSON-запись

Тип MySQL JSON поддерживает массивы JSON и объекты JSON.

#JSON_ARRAY
["xin", 2019, null, true, false, "2019-5-14 21:30:00"]
#JSON_OBJECT
{"key1": "value", "key2": 2019, "time": "2015-07-29 12:18:29.000000"}

Значения JSON_ARRAY и JSON_OBJECT могут быть строками, числами, нулевыми значениями, типами времени и логическими значениями.

Ключ JSON_OBJECT должен быть строкового типа.

Способ вставки: напрямую через форму строки

insert into test_json (obj_json, arr_json) 
       values ('{"key1": "value", "key2": 2019, "time": "2015-07-29 12:18:29.000000"}', 
               '["xin", 2019, null, true, false, "2019-5-14 21:30:00"]');

результат поиска

select * from test_json

img

Метод вставки: через JSON_OBJECT(), JSON_ARRAY()

insert into test_json (obj_json, arr_json) 
    values (JSON_OBJECT('key1', 'insert by JSON_OBJECT', 'key2', 3.14159),
            JSON_ARRAY('Go', 'Ruby', 'Java', 'PHP'));

результат поиска

select * from test_json

img

PS: Оба типа могут быть вложенными.

Поля запроса с типом JSON

Раньше вы могли запрашивать записи, содержащие JSON, с помощью оператора select, и результаты показаны выше.

Что делать, если вы хотите извлечь конкретное значение из поля JSON?

тип ОБЪЕКТА

Форма col->path, где путь выражения $.key

select obj_json->'$."key1"' key1, obj_json->'$."key2"' key2 from test_json;

img

тип МАССИВА

Форма col->path, где путь выражения равен $[index]

select arr_json->'$[0]' index1, arr_json->'$[1]' index2 , arr_json->'$[2]' index3 from test_json;

img

Обновление полей типа JSON

Обновление. Конечно, вы можете перезаписать все поле напрямую через обновление.

Далее краткое введение в обновление в формате JSON

Встроенные функции JSON_SET(), JSON_INSERT(), JSON_REPLACE(), JSON_REMOVE()

JSON_SET() вставляет значение, перезаписывая его, если оно существует

update test_json
    set obj_json = JSON_SET(obj_json, '$."json_set_key"', 'json_set_value', '$.time', 'new time'),
        arr_json = JSON_SET(arr_json, '$[6]', 'seven element', '$[0]', 'replace first') 
    where id = 1;

select * from test_json where id = 1;

ing

JSON_INSERT() вставляет значение, не перезаписывая исходное значение.

update test_json
    set obj_json = JSON_INSERT(obj_json, '$."json_insert_key"', 'json_insert_value', '$."key1"', 'Set existing key'),
        arr_json = JSON_INSERT(arr_json, '$[4]', 'json_insert_value', '$[0]', 'Set existing index') 
    where id = 2;

select * from test_json where id = 2;

img

JSON_REPLACE() перезапишет только исходное значение

update test_json
    set obj_json = JSON_REPLACE(obj_json, '$."key1"', 'json_replace_key1', '$."json_replace_insert"', 'test'),
        arr_json = JSON_REPLACE(arr_json, '$[3]', 'PHP is best language!', '$[5]', 'json_replace_insert')
    where id = 2;

select * from test_json where id = 2;

img

JSON_REMOVE() удалить

update test.test_json
    set obj_json = JSON_REMOVE(obj_json, '$."key1"', '$."Nonexistent key"'),
        arr_json = JSON_REMOVE(arr_json, '$[0]', '$[5]')
    where id = 2;

select * from test_json where id = 2;

img

разное

Если в сохраненном JSON есть кавычки?

нужно экранировать при вставке

insert into test_json (obj_json, arr_json) values ('{"key1":  "test_obj_value1\\""}', '["\\"test_arr_value1\\""]');
insert into test_json (obj_json, arr_json) values (JSON_OBJECT('key1', '\"test_obj_value1 \'single\''), 
                                                   JSON_ARRAY('\"test_arr_value1\" \'single\''));

Запрос

select obj_json->'$."key1"' obj_key1, arr_json->'$[0]' arr_index1 
    from test_json where id in (3, 4);

img

Если результат запроса не хочет сохранять экранирование, он может быть в виде col->>path

select obj_json->>'$."key1"' obj_key1, arr_json->>'$[0]' arr_index1 
    from test_json where id in (3, 4);

img

Функции слияния JSON_MERGE_PRESERVE() и JSON_MERGE_PATCH()

После версии 8.0 предоставляется функция слияния, которая может объединять несколько JSON.

Разница в том, что JSON_MERGE_PATCH() перезапишет исходное значение, а JSON_MERGE_PRESERVE() не перезапишется.

Попробуем (без вставки таблицы)

select
    JSON_MERGE_PATCH(
        JSON_OBJECT('obj_key1', 'obj_value1', 'obj_key2', 'obj_value2'),
        JSON_OBJECT('obj_key2', 'new_obj_value2')
    ) as col1,
    JSON_MERGE_PATCH(
        JSON_ARRAY('arr_index1', 'arr_index2', 'arr_index3'),
        JSON_ARRAY('arr_index4', 'arr_index5', 'arr_index6')
    ) as col12;

img

select
    JSON_MERGE_PRESERVE(
        JSON_OBJECT('obj_key1', 'obj_value1', 'obj_key2', 'obj_value2'),
        JSON_OBJECT('obj_key2', 'new_obj_value2')
    ) as col2,
    JSON_MERGE_PRESERVE(
        JSON_ARRAY('arr_index1', 'arr_index2', 'arr_index3'),
        JSON_ARRAY('arr_index4', 'arr_index5', 'arr_index6')
    ) as col3;

img

Как упоминалось ранее, тип данных JSON поддерживает вложенность, краткая демонстрация

select
    JSON_MERGE_PRESERVE(
        JSON_ARRAY('arr_index1', 'arr_index2'),
        JSON_OBJECT('obj_key1', 'obj_value1')
    ) as col1,
    JSON_MERGE_PRESERVE(
        JSON_OBJECT('obj_key1', 'obj_value1'),
        JSON_ARRAY('arr_index1', 'arr_index2')
    ) as col2;

img

напиши в конце

При использовании полей типа JSON следует тщательно учитывать структуру базы данных, чтобы определить, подходит ли она для использования типа данных JSON. Разработка часто используется в сочетании с другими языками. MySQL, как TRDB, иногда неудобно манипулировать типами данных JSON, такими как сопоставление ORM строго типизированных языков. Необходимость использования типов данных JSON следует рассматривать в свете реальной ситуации.

Использованная литература:Dev.MySQL.com/doc/Furious/…

Категории