PostgreSQL output mutiple queries into array

Question

Working in postgreSQL 9.6:

I have a number of select statements which are each outputting a single row

e.g. select 8 as "ColA", select 20 as "ColB", select 13 as "ColC"

And I need to get these into an array such as:

["ColA",8],["ColB",20],["ColC",13]

I've tried many varieties of row_to_json, array_to_json etc and no joy, can anybody help please?

---

The actual code I have tried is a bit more complicated than described above but the structure of the data is not... here is my existing code (just to show that I have

SELECT "JobTitle" "name",
    (

    select array_to_json(array_agg(row_to_json(d2))) from

    (SELECT 

    (select row_to_json(d2_1) from (select SUM(CASE WHEN "StartDateTime"<(_Date_For + '1 hour'::interval) AND "EndDateTime">(_Date_For) THEN "Effect" ELSE 0 END) "00:00" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_1) 

UNION ALL

(select row_to_json(d2_10) from (select SUM(CASE WHEN "StartDateTime"<(_Date_For + '11 hour'::interval) AND "EndDateTime">(_Date_For + '10 hour'::interval) THEN "Effect" ELSE 0 END) "10:00" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_10) 

UNION ALL

     (select row_to_json(d2_11) from (select SUM(CASE WHEN "StartDateTime"<(_Date_For + '12 hour'::interval) AND "EndDateTime">(_Date_For + '11 hour'::interval) THEN "Effect" ELSE 0 END) "11:00" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_11)

    ) d2

    ) as "data" 

    FROM "tmpDashboardData" x GROUP BY "JobTitle"

Answer 1

Thanks Fdavidov I worked this one out eventually... here is my code for information:

SELECT "JobTitle" "name",
    (
    select array_to_json(array_agg(row_to_json(d2))) from
    (SELECT  
     (select row_to_json(d2_1) from (select '00:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '1 hour'::interval) AND "EndDateTime">(_Date_For) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_1) UNION ALL
     (select row_to_json(d2_2) from (select '01:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '2 hour'::interval) AND "EndDateTime">(_Date_For + '1 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_2) UNION ALL
     (select row_to_json(d2_3) from (select '02:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '3 hour'::interval) AND "EndDateTime">(_Date_For + '2 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_3) UNION ALL
     (select row_to_json(d2_4) from (select '03:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '4 hour'::interval) AND "EndDateTime">(_Date_For + '3 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_4) UNION ALL
     (select row_to_json(d2_5) from (select '04:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '5 hour'::interval) AND "EndDateTime">(_Date_For + '4 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_5) UNION ALL
     (select row_to_json(d2_6) from (select '05:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '6 hour'::interval) AND "EndDateTime">(_Date_For + '5 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_6) UNION ALL
     (select row_to_json(d2_7) from (select '06:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '7 hour'::interval) AND "EndDateTime">(_Date_For + '6 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_7) UNION ALL
     (select row_to_json(d2_8) from (select '07:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '8 hour'::interval) AND "EndDateTime">(_Date_For + '7 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_8) UNION ALL
     (select row_to_json(d2_9) from (select '08:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '9 hour'::interval) AND "EndDateTime">(_Date_For + '8 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_9) UNION ALL
     (select row_to_json(d2_10) from (select '09:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '10 hour'::interval) AND "EndDateTime">(_Date_For + '9 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_10) UNION ALL
     (select row_to_json(d2_11) from (select '10:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '11 hour'::interval) AND "EndDateTime">(_Date_For + '10 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_11) UNION ALL
     (select row_to_json(d2_12) from (select '11:00' "0", SUM(CASE WHEN "StartDateTime"<(_Date_For + '12 hour'::interval) AND "EndDateTime">(_Date_For + '11 hour'::interval) THEN "Effect" ELSE 0 END) "1" from "tmpDashboardData" where "JobTitle"=x."JobTitle") d2_12)
    ) d2
    ) as "data" 
    FROM "tmpDashboardData" x GROUP BY "JobTitle"