Subject: Re: json_build_object, numeric types, and function limits on 100
 arguments
To: pgsql-admin@lists.postgresql.org
References: 
 <CAOC+FBU_qEQJxyw03ag_5jryAjB+43frFLveivx3uH=uNo--1w@mail.gmail.com>
 <20210126135238.GA20826@alvherre.pgsql>
 <CAOC+FBUobjupfTb9BKctomaygUf6_24AZo0QHQ2g2e1GaGL7tQ@mail.gmail.com>
 <2338155.1611697339@sss.pgh.pa.us>
 <CAOC+FBVkTvWHumTdrOC0XBiYZ73NPV760u7+fQ+O2iD7ik6OwA@mail.gmail.com>
From: Thomas Kellerer <shammat@gmx.net>
Message-ID: <f47ac5c6-7558-f2f9-a439-de7fa956884e@gmx.net>
Date: Wed, 27 Jan 2021 08:04:32 +0100
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.23)
 Gecko/20090812 Thunderbird/2.0.0.23 Mnenhy/0.7.6.666
MIME-Version: 1.0
In-Reply-To: 
 <CAOC+FBVkTvWHumTdrOC0XBiYZ73NPV760u7+fQ+O2iD7ik6OwA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Language: de-DE
Content-Transfer-Encoding: quoted-printable
Precedence: bulk

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 "k=
eyone" 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;