Prace s JSON v MySQL (MariaDB)

Posledni dobou se dostavam do stavu, kdy me uz nestaci vydefinovat sloupce v tabulce a nasazet tam hodnoty. Duvodu je mnoho, ale hlavni je predevsim ten, ze potrebuju k radkum prirazovat ruznorody hodnoty, podle nich pak hledat, pracovat – napriklad konfigurace v ramci nejaky aplikace. Zacal jsem se tedy zajimat o JSON funkce v MySQL, nebot JSON dokumenty pouzivam od doby Elasticu vicemene porad.

Jako uplne nejjednodusi priklad vemu tabulku se seznamem domen, ve ktere budu ukladat predem nijak definovane retezce slouzici k otagovani domen.

CREATE TABLE `domains` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `domain` varchar(250) NOT NULL,
 `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
 PRIMARY KEY (`id`),
 UNIQUE KEY `domain` (`domain`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

Do tabulky nacpu par dat:

INSERT INTO `domains` (`domain`, `tags`) VALUES('dbsahosting.org', '{\"tags\":[\"hosting\",\"international\"]}');
INSERT INTO `domains` (`domain`, `tags`) VALUES('hosting-center.cz', '{\"tags\":[\"hosting\",\"czech\"]}');

Vyhledávání v JSON

Pro jednoduche vyhledavani pouzijem JSON_CONTAINS kde prvnim parametrem je sloupec ktery obsahuje JSON, druhym parametrem co hledame, a ve tretim JSON Path kde to hledame.

V nasem pripade budem hledat radky ktery obsahuji tag „czech“. Protoze se jedna o string, je nutne do parametru co hledat zadat opravdu retezec v uvozovkach, v opacnem pripade dostanem Syntax Error. A protoze nase tagy jsou v JSON stromu v „tags“ doplnime cestu kde hledat na $.tags. Vysledny dotaz pak vypada takto:

SELECT * FROM domains WHERE JSON_CONTAINS(tags, '"czech"', "$.tags")

Jako odpoved nasledne dostaneme radek s domenou „hosting-center.cz“.

Vyber vsech hodnot

Pokud potrebuju vytahnout vsechny tagy, muzu pouzit kouzelny dotaz, ktery funguje ale jen v MariaDB (10.3+) a je vhodny do verze 10.6 max, kde uz lze pouzit JSON_TABLE, kterou vsak nemam.

SELECT DISTINCT(JSON_UNQUOTE(JSON_EXTRACT(tags, CONCAT('$.tags[', idx, ']')))) AS tag FROM domains JOIN ( SELECT seq AS idx FROM seq_0_to_10 ) AS indexes WHERE JSON_EXTRACT(tags, CONCAT('$.tags[', idx, ']')) IS NOT NULL

A dalsi si dopisu priste 🙂