I have to select two type of result set from a table and merge them in single json array like
x
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,
Advertisement
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