PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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
Tags:

2

Answers


  1. 0

    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:

    Login or Signup to reply.
  2. 0

    If column types really match between these two tables, you can shorten the query by labelling the rows and building your jsonb right where you aggregate: demo at db<>fiddle

    select jsonb_agg(jsonb_build_object('pos',pos,'type',type,label,color_or_value))
from (select pos, type,'color' as "label",color as "color_or_value" from images
      union all 
      select pos, type,'value' as "label",value from letters )v;
    jsonb_agg
    [{"pos": 0, "type": "image", "color": "red"}, {"pos": 1, "type": "image", "color": "blue"}, {"pos": 2, "type": "text", "value": "book"}]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search