From: Thomas Kellerer <shammat@gmx.net>
To: pgsql-admin@lists.postgresql.org
Subject: Re: json_build_object, numeric types, and function limits on 100 arguments
Date: Wed, 27 Jan 2021 08:04:32 +0100
Message-ID: <f47ac5c6-7558-f2f9-a439-de7fa956884e@gmx.net> (raw)
In-Reply-To: <CAOC+FBVkTvWHumTdrOC0XBiYZ73NPV760u7+fQ+O2iD7ik6OwA@mail.gmail.com>
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>
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;
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: shammat@gmx.net, pgsql-admin@lists.postgresql.org
Subject: Re: json_build_object, numeric types, and function limits on 100 arguments
In-Reply-To: <f47ac5c6-7558-f2f9-a439-de7fa956884e@gmx.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox