Re: json_build_object, numeric types, and function limits on 100 arguments - Mailing list pgsql-admin

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;