Thread: json_build_object, numeric types, and function limits on 100 arguments

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> 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 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;