Unable to create a large json object from a postgreSQL query using json_build_object

Question

I am trying to create a json object from my query using json_build_object as follows:

Select json_agg(json_build_object('first_name',first_name,
                                  'last_name',last_name,
                                  'email',email,
                                  'date_joined',date_joined,
                                  'verification_date',verification_date,
                                  'zip_code',zip_code,
                                  'city',city,
                                  'country',country)) 
from users 
WHERE last_name= 'xyz'

The json object builds fine with above shown number of columns however when i add all column names, the query gets stuck/hung indefinitely and no error message is displayed. I reduce the number of columns in the query and it returns a proper json object. Does anyone have any idea about this? Thanks

I also tried the query after omitting json_agg but still the same result

Answer 1

I am not sure why your query hangs - could be that there is a limit on the number of args - but since you are building each JSON object in a trivial way (attribute names are the same as column names), try using row_to_json like this:

select json_agg(row_to_json(u.*)) from users u WHERE last_name = 'xyz';

Having tens or hundreds of args is not nice anyway.

Answer 2

You could build several object fragments and then merge them all together:

  with fragments as (
    select jsonb_build_object (
        'key1', key1,
        'key2', key2,
        'key3', key3,
        -- additional keys not included for brevity 
        'key50', key50
      ) as fragment1,
    jsonb_build_object (
        'key51', key51,
        'key52', key52,
        'key53', key53,
        -- additional keys not included for brevity 
        'key100', key100
      ) as fragment2
    from some_table
  )
  select fragment1 || fragment2
  from fragments;