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 "
{"dbsname":"archive_dbs","tabname":"UserTable1","seqscans":103,"ti_npdata":17,"ti_pagesize":2048} |
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
------------------------------