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@xxxxxxx> 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;
Wells Oliver
wells.oliver@xxxxxxxxx
wells.oliver@xxxxxxxxx
