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 🙂