PostgreSQL row as jsonb "array", not "object"

Question

We are trying to emulate a proprietary DB, which has "global primary keys", with PostgreSQL. That is what we would have liked to do (for many rows of many tables):

select 'some_table' as table_name, t.*
from some_table t
where id = 1

union all

select 'other_table' as table_name, t.*
from other_table t
where id = 2;

But, of course, that doesn't work, because every table schema is different. Our DBA suggested this instead:

select 'some_table' as table_name, jsonb_agg(t.*) as content
from some_table t
where id = 1

union all

select 'other_table' as table_name, jsonb_agg(t.*) as content
from other_table t
where id = 2;

Which would do it, but we're worried about the amount of data produced/transfered. Basically, every row of each table is returned as one JSONB object contained in a single array. Now what bothers us, is the repetition of the column names for each row.

Could we instead somehow return a JSONB array per row, with only the values, based on the order of the columns in each table?

Answer 1

You could extract the values from the JSONB value and aggregate them back into a JSON array

select 'some_table' as table_name, 
       id, 
       (select jsonb_agg(x.v) from jsonb_each(to_jsonb(t) - 'id') as x(k,v)) as content
from some_table t;

But you can't rely that the order of the array elements corresponds to the order of the columns in the table.

Another alternative (not using JSON) would be to cast the whole row into a text value:

select 'some_table' as table_name,  
       id,
       t::text
from some_table t;

Casting a record to text should preserve the order of the columns as they are defined in the table.