pgsql merge two json arrays

I have to select two type of result set from a table and merge them in single json array like

select col1,col2,col3 from table 1 order by col3 where col1< 2
union all 
select col1,col2,col3 from table 1 order by col1 where colo = 3

Its saying syntax error I believe due to order by.

I need to return this in json array so I am trying like this

SELECT (    SELECT json_agg(row)
            FROM (
         select col1,col2,col3 from table 1 order by col3 where col1< 2 ) row)
UNION ALL
  SELECT (SELECT json_agg(row)
            FROM (
        select col1,col2,col3 from table 1 order by col1 where colo = 3
) row)

Its retiring two json arrays i.e. obvious, How can I make this in single array

Thanks,

Answer

Do the aggregation after the UNION:

select json_agg(t)
from (
  (select col1,col2,col3 from table1 order by col3 where col1< 2)
  union all 
  (select col1,col2,col3 from table1 order by col1 where colo = 3)
) t

Advertisement

Answer