Oh, thanks, that all works too, except the final jsonb_build_object() approach as it blows up when you're past 100 arguments (which I am with 60ish key/value pairs, thus the original issue).
Lots of solid ideas in this thread though, thank you all.
On Tue, Jan 26, 2021 at 11:04 PM Thomas Kellerer <shammat@gmx.net> wrote:
Wells Oliver schrieb am 27.01.2021 um 07:39: > Thanks, Tom. Doing something like: > > with t as ( select somekey, someotherkey from mytable ) select json_agg(t)->0 from t; > > Feels a lot more, errr, natural. Would rather have the object than an > array of 1 containing the object, thus the ->0 but this works well > and feels SQL-ish indeed.
Seems you just want:
select to_jsonb(t) from ( select somekey, someotherkey from my_table ) t
Or if you want nearly all columns, convert the whole row, and remove those you don't want:
select to_jsonb(t) - 'keyone' - 'keytwo' from my_table;
That will return all columns as json keys, but remove the columns named "keyone" and "keytwo"
Or build the JSON object directly without a derived table (or CTE)
select jsonb_build_object('one', somekey, 'two' someotherkey) from my_table;
