Presenting data from 3 similar queries in PostgreSQL

Question

In my PostgreSQL database I have the following schema:

CREATE TABLE survey_results (
    id integer NOT NULL,
    scores jsonb DEFAULT '{}'::jsonb,
    raw jsonb,
    created_at timestamp without time zone,
    updated_at timestamp without time zone  
);

In this table I have the following data:

INSERT INTO survey_results (id, scores, raw, created_at, updated_at)
    VALUES (1, '{"medic": { "social": { "total": "high" } } }', null, now(), now());

INSERT INTO survey_results (id, scores, raw, created_at, updated_at)
    VALUES (2, '{"medic": { "social": { "total": "medium" } } }', null, now(), now());

INSERT INTO survey_results (id, scores, raw, created_at, updated_at)
    VALUES (3, '{"medic": { "social": { "total": "low" } } }', null, now(), now());

INSERT INTO survey_results (id, scores, raw, created_at, updated_at)
    VALUES (4, '{}', '{ "survey": { "denied": true } }', now(), now());

I want to get data from this table in following format:

{
  "positive": {
    "2018-01-15": 2,
  },
  "negative": {
    "2018-01-5": 1,
  }
  "declined": {
    "2018-01-15": 1,
  }
}

I'm able to get this data in 3 separate queries like this:

WITH positive_count AS (
  SELECT
    COUNT(*) AS count_all,
    date(survey_results.created_at)
    FROM "survey_results"
    WHERE (scores#>>'{medic,social,total}' in('high','medium'))
    GROUP BY date(survey_results.created_at)
), negative_count AS (
  SELECT
    COUNT(*) AS count_all,
    date(survey_results.created_at)
    FROM "survey_results"
    WHERE (scores#>>'{medic,social,total}' in('low'))
    GROUP BY date(survey_results.created_at)
), declined_count AS (
  SELECT
    COUNT(*) AS count_all,
    date(survey_results.created_at)
    FROM "survey_results"
    WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')
    GROUP BY date(survey_results.created_at)
)

SELECT * from positive_count;

How can I combine these to get this data with one query in the format I describe? Or maybe formatting this in the following way will be easier:

  date      |positive_count|negative_count| declined_count|
------------+--------------+--------------+----------------
 2018-01-15 |       1      |       1      |        1      |

Here is sql fiddle:

http://sqlfiddle.com/#!17/a9705/2

Thanks in advance.

Answer 1

To get your results it a table:

SELECT
  date(survey_results.created_at),
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium'))) AS positive,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('low'))) AS negative,
  COUNT(*) FILTER (WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')) AS declined
  FROM survey_results
  GROUP BY date(survey_results.created_at)

to get the json format

select 
jsonb_build_object('positive', 
  json_agg(jsonb_build_object(date, positive)) FILTER (WHERE positive <> 0)
) ||
jsonb_build_object('negative',
  json_agg( jsonb_build_object(date, negative)) FILTER (WHERE negative <> 0)
) ||
jsonb_build_object('declined', 
  json_agg(jsonb_build_object(date, declined)) FILTER (WHERE declined <> 0)
)
from (
SELECT
  date(survey_results.created_at) as date,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium'))) AS positive,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('low'))) AS negative,
  COUNT(*) FILTER (WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')) AS declined
  FROM survey_results
  GROUP BY date(survey_results.created_at)
) as t1;

Or json based on your original query

WITH positive_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*)
    ) as j
    FROM "survey_results"
    WHERE (scores#>>'{medic,social,total}' in('high','medium'))
    GROUP BY date(survey_results.created_at)
), negative_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*)
    ) as j
    FROM "survey_results"
    WHERE (scores#>>'{medic,social,total}' in('low'))
    GROUP BY date(survey_results.created_at)
), declined_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*)
    ) as j
    FROM "survey_results"
    WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')
    GROUP BY date(survey_results.created_at)
)
SELECT
jsonb_build_object('positive', (SELECT json_agg(j) from positive_count)) ||
jsonb_build_object('negative', (SELECT json_agg(j) from negative_count)) ||
jsonb_build_object('declined', (SELECT json_agg(j) from declined_count))
;

EDIT: after the comments, keeping entries with 0

WITH positive_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium')))
    ) as j
    FROM "survey_results"
    GROUP BY date(survey_results.created_at)
), negative_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('low')))
    ) as j
    FROM "survey_results"
    GROUP BY date(survey_results.created_at)
), declined_count AS (
  SELECT
    jsonb_build_object(
      date(survey_results.created_at),
      COUNT(*) FILTER (WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true'))
    ) as j
    FROM "survey_results"
    GROUP BY date(survey_results.created_at)
)
SELECT
jsonb_build_object('positive', (SELECT json_agg(j) from positive_count)) ||
jsonb_build_object('negative', (SELECT json_agg(j) from negative_count)) ||
jsonb_build_object('declined', (SELECT json_agg(j) from declined_count))

---

EDIT for structure with date first (see comment)

Based on my query (this is more efficient, as it queries the table only once)

select 
json_agg(
jsonb_build_object(date, 
  jsonb_build_object('positive', positive)  ||
  jsonb_build_object('negative', negative)  ||
  jsonb_build_object('declined', declined)
))
from (
SELECT
  date(survey_results.created_at) as date,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('high','medium'))) AS positive,
  COUNT(*) FILTER (WHERE (scores#>>'{medic,social,total}' in('low'))) AS negative,
  COUNT(*) FILTER (WHERE (coalesce(raw#>>'{survey,denied}','f') = 'true')) AS declined
  FROM survey_results
  GROUP BY date(survey_results.created_at)
) as t1;

Also the WITH query would be more complicated, since each of the 3 queries returns the dates, but we need one source only. Of course after the condition went into FILTER all 3 WITH queries return the same rows (just a diff count). So we could take the date from any of them. But then why run 3 queries, when one is enough?

We do need 2 queries though (1 sub-query, as I have) since there are 2 separate aggregation: 1) aggregate by date 2) aggregate the results of this aggregation into 1 single row.

Answer 2

Your JSON format is a bit tricky to implement. You try to use the date value as key. This means, with more dates you will have a wide range of keys in your JSON blob.

Instead, I would create a list within each category. Each list element has two keys created_at and count and can be repeated.

{
  "positive": {[{"created_at": "2018-01-15","count" :2},{"created_at": "2018-01-14","count" :1}]},
  "negative": {[{"created_at": "2018-01-15","count" :1},{"created_at": "2018-01-14","count" :3}]},
  "declined": {[{"created_at": "2018-01-15","count" :1},{"created_at": "2018-01-14","count" :1}]}
}

This query gets you there.

WITH status_count AS (
         SELECT
                CASE
                    WHEN (survey_results.scores #>> '{medic,social,total}'::text[]) = ANY (ARRAY['high'::text, 'medium'::text]) THEN 'positive'::text
                    WHEN (survey_results.scores #>> '{medic,social,total}'::text[]) = 'low'::text THEN 'negative'::text
                    WHEN COALESCE(survey_results.raw #>> '{survey,denied}'::text[], 'f'::text) = 'true'::text THEN 'declined'::text
                    ELSE NULL::text
                END AS status,
            date(survey_results.created_at) AS created_at
           FROM survey_results
        )
 SELECT row_to_json(t.*) AS row_to_json
   FROM ( SELECT ( SELECT array_to_json(array_agg(row_to_json(d.*))) AS array_to_json
                   FROM ( SELECT status_count.created_at,
                            count(status_count.status) AS count
                           FROM status_count
                          WHERE status_count.status = 'positive'::text
                          GROUP BY status_count.created_at, status_count.status) d) AS positive,
            ( SELECT array_to_json(array_agg(row_to_json(d.*))) AS array_to_json
                   FROM ( SELECT status_count.created_at,
                            count(status_count.status) AS count
                           FROM status_count
                          WHERE status_count.status = 'negative'::text
                          GROUP BY status_count.created_at, status_count.status) d) AS negative,
            ( SELECT array_to_json(array_agg(row_to_json(d.*))) AS array_to_json
                   FROM ( SELECT status_count.created_at,
                            count(status_count.status) AS count
                           FROM status_count
                          WHERE status_count.status = 'declined'::text
                          GROUP BY status_count.created_at, status_count.status) d) AS declined) t;

For the simple table option you will need to create a pivot table. You will need to install the tablefunc extension for this.

CREATE EXTENSION tablefunc

Afterwards you can run the query below which gives you the results you want.

SELECT ct.created_at,
    ct.positive_count,
    ct.negative_count,
    ct.declined_count
FROM crosstab('
  WITH status_count AS 
    (SELECT
         CASE
          WHEN (scores#>>''{medic,social,total}'' in (''high'',''medium'')) THEN ''positive''
          WHEN (scores#>>''{medic,social,total}'' in(''low'') ) THEN ''negative''
          WHEN (coalesce(raw#>>''{survey,denied}'',''f'') = ''true'') THEN ''declined''
         END AS status,
         date(survey_results.created_at) AS created_at
     FROM survey_results)
   SELECT created_at, status, count(status) FROM status_count GROUP BY created_at, status ORDER BY created_at, status'::text) ct(created_at date, declined_count bigint, negative_count bigint, positive_count bigint);