I have a CloudWatch log stream that contains structured records like the following:

I'd like to query the counts of each type by the env (to graph them)

Something equivalent to the following SQL statement:

SELECT 
   SUM(CASE WHEN env = 'dev' THEN 1 ELSE 0 END) AS dev, 
   SUM(CASE WHEN env = 'prod' THEN 1 ELSE 0 END) AS prod 
FROM TABLE 
GROUP BY type

I've written the following query:

fields @timestamp, record.type as type, record.env as env
| stats count(env="dev") as dev, count(env="prod") as prod by type

expecting to get the following result:

But unfortunately, I'm getting the same count for both columns regardless of the applied filter inside the count() as follows:

How can I achieve the output I need

Note

I can do the following query to get the results grouped by each env:

fields @timestamp, record.type as type, record.env as env
| stats count(*) by type, env

I'll get :

But I need the pivoted version