JSON a MySQL

26. 10. 2016 | Martin V.

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 e-shopy, weby, 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 klienty patří například Skupina ČEZ, RunCzech, Pojišťovna České Spořitelny, PROBIO a Kancelář prezidenta republiky.

Na seznam článků

Přeskočit na hlavní nabídku