JSON a MySQL

A co nám JSON datový typ vlastně přináší? Především toto:

• automatickou validaci ukládaných dat – pokud je ukládaný JSON dokument nevalidní, tak operace končí chybou
• možnost provádět operace s daty v JSON dokumentu – data jsou uložená v binární formě, databáze nemusí při operacích nejprve parsovat data z textového formátu
• možnost vytvořit si nad libovolným atributem index – to se nicméně neprování přímo, ale pomocí další nové vlastnosti MySQL, generovaných sloupců

Dále bych zmínil ještě omezení JSON datového typu:

• maximální velikost uloženého JSON dokumentu je dána systémovou proměnnou max_allowed_packet, jejíž hodnota se defaultně nastavuje na velikost 4 MB
• nelze definovat DEFAULT value

Tolik k základním informacím, nyní následuje malá ukázka.

Vytvořme si jednoduchou tabulku:

1

create table T_FORM_DATA ( `id` int not null auto_increment, `data` json, `dataId` INT GENERATED ALWAYS AS (JSON_EXTRACT(`data`, '$.id')), primary key (`id`), index IX_FORM_DATA_dataId (`dataId`) ) engine = InnoDb;

Jak vidíte, tabulka obsahuje sloupec data typu JSON a také sloupec dataId s nestandardní definicí. Zde se jedná právě o generovaný sloupec, kdy se pomocí funkce JSON_EXTRACT získá z JSON dokumentu hodnota z atributu id. Nad tímto generovaným sloupcem pak je vytvořen index a lze tak efektivně hodnotu z JSON dokumentu použít k indexaci a vyhledávání.

Vložení dat do tabulky provedeme běžným způsobem:

1

insert into T_FORM_DATA (`data`) values ('{"id": 10, "firstName": "Johnny", "lastName": "Mahoney"}');

Jak vidíte, data se vkládají jako běžný textový řetězec, není tedy nutné kvůli práci s JSONem upravovat knihovny pracující s databází. Zkoušel jsem v jednoduchých testech ukládat data jak přes ADAMa, tak přes MyBatis, obojí normálně funkční. Jen malé upozornění právě k testům - díky tomu, že si databáze rozparsuje JSON dokument a ukládá si ho v binární formě, tak není garantováno, že při načtení JSON dokumentu z databáze budou data v tom pořadí, v jakém byla uložena. Například výše uvedená data se vrací z DB v této podobě:

1

{"id": 10, "lastName": "Mahoney", "firstName": "Johnny"}

To znamená, že můj naivní Unit test, který porovnával vložený JSON a načtený JSON, hlásí chybu, byť jsou data správně.

Pokud vložíte záznam, který neobsahuje atribut použitý v generovaném sloupci, pak se do tohoto sloupce nastaví buď NULL, pokud je povolen, nebo je vrácena chyba, že sloupec musí obsahovat údaje, tedy logické chování.

A to je v podstatě dostačující základ, který asi budeme zpočátku v našich aplikacích používat, tedy uložení/načtení JSON dokumentu a možnost hledání nad vybraným atributem z dokumentu.

Databáze nicméně nabízí celou řadu funkcí pro operaci s JSON dokumentem, doporučuji se podívat alespoň na přehled funkcí.

Když jsem si jednotlivé funkce alespoň zběžně procházel, tak mě zaujala funkce JSON_OBJECT, která slouží pro vytvoření nového JSON dokumentu a napadlo mě její využití. Stále častěji se potkávám s požadavkem webařů vracet data v podobě JSONu a pomocí této funkce si můžete vytáhnout data z požadované tabulky a rovnou je převést do JSON dokumentu. Ukázka nad všem známou tabulkou USER:

1

select JSON_OBJECT('id', `id`, 'login', `login`, 'firstName', `firstName`, 'lastName', `lastName`) from USER where `id` = 1;

Výsledkem pak je JSON dokument:

1

{"id": 1, "login": "veska+1@fg.cz", "lastName": "Veska", "firstName": "Martin"}

Funkci lze samozřejmě použít i při vytváření pohledů, takže tento způsob je použitelný při dotazování přes ADAMa.

Tolik základní informace k novému datovému typu JSON.

autor: Martin Veska, Senior Application Developer