Výběr dat z tabulky do JSON formátu

V diskusní skupině postgresql-cz@googlegroups.com byla jednou diskuse jak vybrat tabulky do JSON formatu, které se zůčastnil Pavel Sněhule,  Jan Michálek a já. Domnívám se, že výsledky jsou tak zajímavé, že stojí za to to zveřejnit závěry, které jsem si odnesl z této diskuse.

 

Základ :

Máme tabulku

create table xx
( a int,
b int,
c int);


insert into xx
values
(10,20,30),
(40,50,null);

– 1. pripad

– a. varianta

Dle Pavla Shnehuleho je tohle patern, který se používá.
Vytvoří se recordset na který je pak možné uplatnit funkci row_to_json.

select row_to_json(r) from (select a, b + coalesce(c, 0) as expr from xx) r;

– b.varianta

V tomhle případě, lze se dotázat i přímo.
select row_to_json) from xx;

– c.varianta

Nebo vše převést na text a ten na JSON
select (‚{„a“:‘||a||‚,„expr“:‘||(b+ coalesce(c, 0))||‚}‘)::JSON from xx;

Zde je výsledek selectu :

„{a
“{a

– 2.pripad

– a.varianta

-- ustálený patern
select row_to_json® from (select a, b + coalesce(c, 0) as expr, row(a,b,c) as data  from xx) r;

– b.varianta

-- lze provést i takto
select row_to_json)  from xx;

– c.varianta

-- převod přes text a přetypování na JSON
select (‚{„a“:‘||a||‚,„expr“:‘||(b + coalesce(c, 0))||‚,„data“:‘||row_to_json||‚}‘)­::JSON from xx;

Výsledek selectu :

„{a
“{a

– 3.pripad

-- 3× vnorený select

– a.varianta

select row_to_json® from (select a, b + coalesce(c, 0) as expr,
(select x from (select 10 as c1,20 as c2 ,20 as c3) x) as data  from xx) r;

– b.varianta

-- jednoduchsi varianta
select row_to_json)  from xx;

– c. varianta

-- do textu a z textu do JSON
select (‚{„a“:‘||a||‚,„expr“:‘||(b + coalesce(c, 0))||‚,„data“:{„c1“:‘||10||‚,„c2“:­‘||20||‚,„c3“:‘||20||‚}}‘)::JSON from xx;

– d.varianta

-- To samé, ale s využitim typů
create type typea as
(c1 int,
c2 int,
c3 int);

create type typeb as
(a int,
expr int,
data typea);

select row_to_json(cast,cast as typeb)) from xx;

Výsledek :
„{a
“{a

Dodejme, že autorem všech variant a je Pavel Sněhule a ostatních já.