JSON a MySQL

26. 10. 2016

MySQL verze 5.7 přináší mj. nový datový typ JSON. S JSON formátem pracujeme v našich projektech čím dál častěji, takže stojí za zvážení, zda nezačít tento datový typ na nových projektech využívat.

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

FG Forrest, a.s., je internetovou agenturou založenou v roce 1996.

Navrhuje optimální využití digitálních médií a kompletně realizuje zejména webové prezentace, intranety a mobilní aplikace. Vlastní produkce, profesionální přístup, redakční systém Edee CMS a prokazatelné přínosy jsou důvody, proč vybrat za partnera FG Forrest. Mezi dlouhodobé klienty patří Skupina ČEZ, McDonald's, Komerční banka, AC Sparta Praha nebo Kancelář prezidenta republiky.

Na seznam článků

Přeskočit na hlavní nabídku