question on genBSON()
Playing around with genbson() to create some JSON output, I have the following query:
database sysmaster;
select first 10 genbson(ROW(p.dbsname, p.tabname, p.seqscans, t.ti_npdata, t.ti_pagesize), 1, 1)::JSON
from sysptprof p
inner join systabinfo t on t.ti_partnum = p.partnum
where p.tabname not like 'sys%'
and p.tabname not matches 'ph_*'
and p.dbsname not like 'sys%'
order by p.seqscans desc;The first line of output generated by the above is "
But I want to change the labels 'ti_npdata' and 'ti_pagesize' to 'data_pages' and 'page_bytes'. I attempted to add "AS
newname
" in the projection clause, but I believe the fact that this is inside the ROW() block is causing problems. What I ended up with is:
select first 10 genbson(ROW(p.dbsname, p.tabname, p.seqscans, t.ti_npdata as data_pages, t.ti_pagesize as page_size), 1, 1)::JSON
from sysptprof p
inner join systabinfo t on t.ti_partnum = p.partnum
where p.tabname not like 'sys%'
and p.tabname not matches 'ph_*'
and p.dbsname not like 'sys%'
order by p.seqscans desc;The result of this is a -201 syntax error.
As a simple SELECT, it would work:
select p.dbsname, p.tabname, p.seqscans, t.ti_npdata as data_pages, t.ti_pagesize as page_bytes
from sysptprof p
inner join systabinfo t on t.ti_partnum = p.partnum
where p.tabname not like 'sys%'
and p.tabname not matches 'ph_*'
and p.dbsname not like 'sys%'
order by p.seqscans desc;Any suggestions on how to get the desired labels in the JSON output?
------------------------------
mark collins
------------------------------