Query to get records group by month

SQL++
1

Hi All,
Our data looks like
{ "nsid": "event", "someId": "12345", "events": { "ABC": [ { "event": "PRESENTED", "createdDate": 1565722324820 }, { "event": "COMPLETED", "createdDate": 1565722267263 } ] } }
Need output over time like
For “ABC”
Month -Presented- Completed
JAn - 1 - 0
Feb - 1 - 0
March - 1 - 1

Any help will be really appreciated.

2 
SELECT DATE_PART_MILLIS(ev.createdDate, "month") AS month,  
         SUM(CASE WHEN ev.event = "PRESENTED" THEN 1 ELSE 0 END) AS presented,
         SUM(CASE WHEN ev.event = "COMPLETED" THEN 1 ELSE 0 END) AS completed
FROM default AS d
UNNEST d.events.ABC AS ev
GROUP BY DATE_PART_MILLIS(ev.createdDate, "month");
2 Likes
3

@vsr1 this works great when I have a primary index. But primary index is not allowed in production for us. Should I create a index on events?

CREATE INDEX idxEvents ON default (ALL OBJECT_PAIRS(events));

4

Depends on what query is and what is predicate of query.

CREATE INDEX ix1 ON default (ALL ARRAY ep.name FOR ep IN OBJECT_PARIS(events) END) WHERE nsid = "event";

SELECT DATE_PART_MILLIS(ev.createdDate, "month") AS month,  
         SUM(CASE WHEN ev.event = "PRESENTED" THEN 1 ELSE 0 END) AS presented,
         SUM(CASE WHEN ev.event = "COMPLETED" THEN 1 ELSE 0 END) AS completed
FROM default AS d
UNNEST OBJECT_PARIS(d.events) AS ep
UNNEST ep.val AS ev
WHERE d.nsid = "event" AND ep.name = "ABC"
GROUP BY DATE_PART_MILLIS(ev.createdDate, "month");
5

The query returns rows with presented and completed as 0 as ep.val is an array

6

You need another UNNEST. Updated query try again

1 Like