pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedjson_build_object, numeric types, and function limits on 100 arguments
7+ messages / 4 participants
[nested] [flat]
* json_build_object, numeric types, and function limits on 100 arguments
@ 2021-01-25 22:41 Wells Oliver <wells.oliver@gmail.com>
2021-01-26 13:52 ` Re: json_build_object, numeric types, and function limits on 100 arguments Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 1 reply; 7+ messages in thread
From: Wells Oliver @ 2021-01-25 22:41 UTC (permalink / raw)
To: pgsql-admin
Hi. I am trying to build a Large JSON Object, and running into issues with
the function limit on 100 arguments. I know I can use ARRAY[] to get around
this, but I can't mix text and numeric types, which I need to do to have a
nicely usable JSON object.
I know I can do this: json_build_object('somekey', 1.0, 'someotherkey',
2.0) but cannot put more than 100.
What I can't do is json_build_object(array['somekey', 1.0, 'someotherkey',
2.0]); (json_object() with the same error): invalid input syntax for type
numeric: "somekey"
What I can do but don't want to do is json_object(array['somekey', '2.0',
'someotherkey', '2.0']) because you have a poorly typed JSON object that's
less helpful downstream.
Any options for me?
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: json_build_object, numeric types, and function limits on 100 arguments
2021-01-25 22:41 json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
@ 2021-01-26 13:52 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-01-26 19:10 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Alvaro Herrera @ 2021-01-26 13:52 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: pgsql-admin
On 2021-Jan-25, Wells Oliver wrote:
> Hi. I am trying to build a Large JSON Object, and running into issues with
> the function limit on 100 arguments. I know I can use ARRAY[] to get around
> this, but I can't mix text and numeric types, which I need to do to have a
> nicely usable JSON object.
>
> I know I can do this: json_build_object('somekey', 1.0, 'someotherkey',
> 2.0) but cannot put more than 100.
Maybe you can use the jsonb || jsonb operator:
select json_build_object('somekey', 1.0, 'someotherkey', 2.0)::jsonb || json_build_object('thirdkey', 3.0)::jsonb;
?column?
────────────────────────────────────────────────────────
{"somekey": 1.0, "thirdkey": 3.0, "someotherkey": 2.0}
(1 fila)
--
Álvaro Herrera 39°49'30"S 73°17'W
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: json_build_object, numeric types, and function limits on 100 arguments
2021-01-25 22:41 json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 13:52 ` Re: json_build_object, numeric types, and function limits on 100 arguments Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2021-01-26 19:10 ` Wells Oliver <wells.oliver@gmail.com>
2021-01-26 21:42 ` Re: json_build_object, numeric types, and function limits on 100 arguments Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 7+ messages in thread
From: Wells Oliver @ 2021-01-26 19:10 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-admin
Yeah, thanks, I thought about that: concatenating a few different objects
to make a bigger object. Seemed silly, but if there's not a cleaner
solution, it does work.
On Tue, Jan 26, 2021 at 5:52 AM Alvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
> On 2021-Jan-25, Wells Oliver wrote:
>
> > Hi. I am trying to build a Large JSON Object, and running into issues
> with
> > the function limit on 100 arguments. I know I can use ARRAY[] to get
> around
> > this, but I can't mix text and numeric types, which I need to do to have
> a
> > nicely usable JSON object.
> >
> > I know I can do this: json_build_object('somekey', 1.0, 'someotherkey',
> > 2.0) but cannot put more than 100.
>
> Maybe you can use the jsonb || jsonb operator:
>
> select json_build_object('somekey', 1.0, 'someotherkey', 2.0)::jsonb ||
> json_build_object('thirdkey', 3.0)::jsonb;
> ?column?
> ────────────────────────────────────────────────────────
> {"somekey": 1.0, "thirdkey": 3.0, "someotherkey": 2.0}
> (1 fila)
>
>
> --
> Álvaro Herrera 39°49'30"S 73°17'W
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: json_build_object, numeric types, and function limits on 100 arguments
2021-01-25 22:41 json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 13:52 ` Re: json_build_object, numeric types, and function limits on 100 arguments Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-01-26 19:10 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
@ 2021-01-26 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-27 06:39 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2021-01-26 21:42 UTC (permalink / raw)
To: Wells Oliver <wells.oliver@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-admin
Wells Oliver <wells.oliver@gmail.com> writes:
> Yeah, thanks, I thought about that: concatenating a few different objects
> to make a bigger object. Seemed silly, but if there's not a cleaner
> solution, it does work.
A more SQL-ish way to deal with this might be to use jsonb_agg()
or jsonb_object_agg().
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: json_build_object, numeric types, and function limits on 100 arguments
2021-01-25 22:41 json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 13:52 ` Re: json_build_object, numeric types, and function limits on 100 arguments Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-01-26 19:10 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 21:42 ` Re: json_build_object, numeric types, and function limits on 100 arguments Tom Lane <tgl@sss.pgh.pa.us>
@ 2021-01-27 06:39 ` Wells Oliver <wells.oliver@gmail.com>
2021-01-27 07:04 ` Re: json_build_object, numeric types, and function limits on 100 arguments Thomas Kellerer <shammat@gmx.net>
0 siblings, 1 reply; 7+ messages in thread
From: Wells Oliver @ 2021-01-27 06:39 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-admin
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.
On Tue, Jan 26, 2021 at 1:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Wells Oliver <wells.oliver@gmail.com> writes:
> > Yeah, thanks, I thought about that: concatenating a few different objects
> > to make a bigger object. Seemed silly, but if there's not a cleaner
> > solution, it does work.
>
> A more SQL-ish way to deal with this might be to use jsonb_agg()
> or jsonb_object_agg().
>
> regards, tom lane
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: json_build_object, numeric types, and function limits on 100 arguments
2021-01-25 22:41 json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 13:52 ` Re: json_build_object, numeric types, and function limits on 100 arguments Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-01-26 19:10 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 21:42 ` Re: json_build_object, numeric types, and function limits on 100 arguments Tom Lane <tgl@sss.pgh.pa.us>
2021-01-27 06:39 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
@ 2021-01-27 07:04 ` Thomas Kellerer <shammat@gmx.net>
2021-01-27 07:10 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas Kellerer @ 2021-01-27 07:04 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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;
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: json_build_object, numeric types, and function limits on 100 arguments
2021-01-25 22:41 json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 13:52 ` Re: json_build_object, numeric types, and function limits on 100 arguments Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-01-26 19:10 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 21:42 ` Re: json_build_object, numeric types, and function limits on 100 arguments Tom Lane <tgl@sss.pgh.pa.us>
2021-01-27 06:39 ` Re: json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-27 07:04 ` Re: json_build_object, numeric types, and function limits on 100 arguments Thomas Kellerer <shammat@gmx.net>
@ 2021-01-27 07:10 ` Wells Oliver <wells.oliver@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Wells Oliver @ 2021-01-27 07:10 UTC (permalink / raw)
To: Thomas Kellerer <shammat@gmx.net>; +Cc: pgsql-admin@lists.postgresql.org
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;
>
>
>
>
--
Wells Oliver
wells.oliver@gmail.com <wellsoliver@gmail.com>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2021-01-27 07:10 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-01-25 22:41 json_build_object, numeric types, and function limits on 100 arguments Wells Oliver <wells.oliver@gmail.com>
2021-01-26 13:52 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2021-01-26 19:10 ` Wells Oliver <wells.oliver@gmail.com>
2021-01-26 21:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-01-27 06:39 ` Wells Oliver <wells.oliver@gmail.com>
2021-01-27 07:04 ` Thomas Kellerer <shammat@gmx.net>
2021-01-27 07:10 ` Wells Oliver <wells.oliver@gmail.com>
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