Search code examples
sqlarrayspostgresqlaggregate-functionsjsonb

How to suppress key for elements of aggregated JSON array

Working with jsonb and Postgres I'm running into the following problem:

My output looks like this: [ {columnName: {}}, { columnName: {} } ] But I would like it to look like this: [ {}, {} ]

I want to put all of the objects in a jsonb array, but without the column names being added.

Here is the basic query, which can be seen in this fiddle: http://sqlfiddle.com/#!17/b701b8/2

I've noticed that when using array_agg(), the column names are not added, but with JSON they do get added. How to remove the column names so that I can get the desired output using JSON?

select json_agg(v)
from (
  select json_build_object('pos',pos, 'type',type, 'color',color)
  from (select * from images)a
  union all 
  select json_build_object('pos',pos, 'type',type, 'value',value)
  from (select * from letters)b
)v
Solution

  • Aggregate the column instead of the whole row:

    SELECT json_agg(v.image) AS images
FROM  (
   SELECT json_build_object('pos',pos, 'type',type, 'color',color) AS image  -- !
   FROM   images i -- simpler

   UNION ALL
   SELECT json_build_object('pos',pos, 'type',type, 'value',value)
   FROM   letters l  -- simpler
   ) v;

    Notably, your query produces json, not jsonb.
    Simpler while nesting whole rows:

    SELECT json_agg(v.image) AS images
FROM  (   
   SELECT to_json(i) AS image
   FROM   images i

   UNION ALL
   SELECT to_json(l)
   FROM   letters l
   ) v;

    Similar for jsonb (where whitepace is a non-issue)

    SELECT jsonb_agg(v.image) AS images
FROM  (   
   SELECT to_jsonb(i) AS image
   FROM   images i

   UNION ALL
   SELECT to_jsonb(l)
   FROM   letters l
   ) v;

    fiddle

    See: