PostgreSQL : Anti antipattern sloupce jsonb

Tento můj příspěvek je reakce na článek craing ringer PostgreSQL anti-patterns: Unnecessary json/hstore dynamic column.

V tomto článku autor popisuje stručně nedostatky ukládání dat jsonb strukutr v sloupci. Nechci je kritizovat, protože sám jsem přívržencem ralčního ukládání dat do databáze. Na druhé straně jsem zkusil jak bzch vše udělal tak, aby vše fungovalo i s jsonb v jednom sloupci. Umím si představit situace, kdy je nutno uchovávat v strukutúře jsonb primární data a ty až po např: schválení, nebo definitivním uložení převést do klasického tabulkovo relačního nebo objektového uložení dat.

Pro ty co článek ještě nečetli tak zde stručná rekapitulace:

Máme tabulku :

CREATE TABLE people( id serial primary key, data jsonb not null );

Jak si každý všimne, je zde sloupec data typu json. Lze do něj uložit následující data :

INSERT INTO people(data) VALUES ($$ { "name": "Bob", "addresses": [ { "street": "Centre", "streetnumber": 24, "town": "Thornlie", "state": "WesternAustralia", "country": "Australia" }, { "street": "Example", "streetnumber": "4/311-313", "town": "Auckland", "country": "NewZealand" } ], "phonenumbers": [ { "type": "mobile", "number": "12345678" } ] } $$); INSERT INTO people(data) VALUES ($$ { "name": "Fred", "phonenumbers": [ { "type": "mobile", "number": "12345678" } ] } $$); INSERT INTO people(data) VALUES ($$ { "name": "John Doe" } $$);

Dle autora článku potíže proč upřednosťovat ERD před jsonb jsou následující. A moje dodatky jak to lze vše obejít.

1. Baroque queries

Jak vyhledat lidi, kteří mají stejné telefonní číslo ?
Já bych to řešil následovně :

select vys1.id, vys2.id, vys1.number from (select p1.id, json_array_elements((p1.data->>'phonenumbers')::json)->>'number' as number, json_array_elements((p1.data->>'phonenumbers')::json)->>'type' as type from people p1 where (p1.data ->> 'phonenumbers') is not null ) vys1, (select p1.id, json_array_elements((p1.data->>'phonenumbers')::json)->>'number' as number, json_array_elements((p1.data->>'phonenumbers')::json)->>'type' as type from people p1 where (p1.data ->> 'phonenumbers') is not null ) vys2 where vys1.type = 'mobile' and vys2.type = 'mobile' and vys1.number = vys2.number and vys1.id > vys2.id

Pak dostaneme požadovaný výsledek:


id id number

---

18; 16; „12345678“

Co jsem udělal ?
Proměnil jsem původní json v řádku na normální řádek jako v tabulce a pak jsem s touto tabulkou pracoval jako s normální.

2. No fixed data typing

Autor píše, že json není typové uložení dat, jak je v tomu databázových tabulkách a proto např: ‚12345678‘ a ‚„12345678“‘ nejsou ekvivalentní výrazy. Ale mohou být v případě, že na to pamatujeje
a vytvoříme dotaz jako je tento :

SELECT '{"type": "mobile", "number": "12345678"}'::jsonb, '{"type": "mobile", "number": 12345678}'::jsonb, '{"type": "mobile", "number": "12345678"}'::jsonb->>'number' = '{"type": "mobile", "number": 12345678}'::jsonb->>'number' as "isequal";

Pak výsledek je přesně takový, jak je požadovaný.

jsonb jsonb isequal "{"type": "mobile", "number": "12345678"}"; "{"type": "mobile", "number": 12345678}"; t

3. No constraints

Píše, že v jsonb lze vložit i následující příkaz, ačkoliv je špatný, protože type neodpovídá hodnotě z číselníku a číslo obsahuje mezeru.

insert into people (data) values ('{"phonenumbers": [{"type":"Mobile","number":"1234 5678"}]}');

Lze tomu předejít tak, že napíšeme funkci, která při vložení nebo editaci json bude kontrolovat jeho strukturu.

CREATE OR REPLACE FUNCTION f_people_chck(data jsonb) RETURNS boolean AS $BODY$ DECLARE ptype text; pnumber text; lnumber int; ldata jsonb; cur CURSOR FOR SELECT json_array_elements((data->>'phonenumbers')::json)->>'number' as number, json_array_elements((data->>'phonenumbers')::json)->>'type' as type; BEGIN open cur; loop FETCH cur INTO pnumber,ptype; if not found then exit; end if; lnumber = cast(pnumber as int); IF (ptype 'mobile') THEN RAISE 'tag "type" contains wrong value : %', ptype USING ERRCODE = '20000'; return false; END IF; end loop; CLOSE cur; return true; END $BODY$ LANGUAGE plpgsql VOLATILE COST 100;

Nad tabulkou pověsíme do check volání vyše popsanou funkci.

CREATE TABLE people( id serial primary key, data jsonb not null check (f_people_chck(data)) );

Pak kdybych se pokusil vlozit data z clanku :

insert into people (data) values ('{"phonenumbers": [{"type":"Mobile","number":"1234 5678"}]}');

Zobrazila by se tato zprava :

ERROR: invalid input syntax for integer: "1234 5678" CONTEXT: PL/pgSQL funkce f_people_chck(jsonb) řádek 16 na přiřazení ********** Chyba ********** ERROR: invalid input syntax for integer: "1234 5678" Stav SQL: 22P02 Kontext:PL/pgSQL funkce f_people_chck(jsonb) řádek 16 na přiřazení

Opravim chybu a ponecham prvni:

insert into people (data) values ('{"phonenumbers": [{"type":"Mobile","number":"12345678"}]}');

Zobrazi se nasledujici zprava :

ERROR: tag "type" contains wrong value : Mobile ********** Chyba ********** ERROR: tag "type" contains wrong value : Mobile Stav SQL: 20000

Jsem zastánce relačního ukládani a prace s daty, ale i s json se da kouzlit.

Tímto jsme udělali typovou kontrolu. Určitě by šli tímto způsobem projít i další kontroly. PostgreSQL je skvělý nástroj pro ukládání a práci s daty. Jenom procedúry už by se mohli uzavírat transakce.