Counting every time a value is equal to TRUE

I have a postgreSQL DB that stores UTC time field and a data field with JSON in it from a Nest thermostat. In the data from Nest I get a value that tells me if the AC is ON (true) or OFF (false). I want to be able to count the times it is either true (ON) or false (OFF) and graph it. The data looks like this and I get it every minute:

{ "current_state":{ "mode":"cool", "temperature":70.50200000000000954969436861574649810791015625, "humidity":54, **"ac":false,** "heat":false, "alt_heat":false, "fan":false, "auto_away":0, "manual_away":false, "structure_away":false, "leaf":true, "battery_level":3.91199999999999992184029906638897955417633056640625, "active_stages":{ }, "eco_mode":"schedule", "eco_temperatures":{ } }, "target":{ "mode":"cool", "temperature":84.6999680000000125801307149231433868408203125, "time_to_target":0 }, "name":"Upstairs Office \/ RTU-11", "auto_cool":84, "auto_heat":false }

I am trying to graph data->‘current_state’->>‘ac’ when its set to true

But you are asking about the SQL query or any other Grafana issue to take into account?

I am actually asking for the SQL. I am trying to count the number of minutes the AC STATE is TRUE.

Here is my SQL so far and it only returns 1 as the answer.

SELECT "time" AS time, count(*) FROM conditions WHERE data->'rtus'->'rtu-6'->'rtu-info'->'current_state'->>'ac' = 'true' AND $__timeFilter("time") group by data->'rtus'->'rtu-6'->'rtu-info'->'current_state'->>'ac', time

This is what I get: https://www.dropbox.com/s/g6lwawl7ozsb4rl/Screenshot%202019-10-10%2019.34.48.png?dl=0

This question is not related with Grafana but I try help you. ac field is varchar o boolean datatype? because you’re considering in your query like varchar/char. After that, you can create mappings between values and labels.

It is a varchar in this case. I guess mapping is what I am looking to do.

SELECT
  time as "time",
  data->'rtus'->'rtu-6'->'rtu-info'->'current_state'->>'ac' as "data2",
  count(data->'rtus'->'rtu-6'->'rtu-info'->'current_state'->>'ac')
FROM conditions
WHERE
  data->'rtus'->'rtu-6'->'rtu-info'->'current_state'->>'ac' = 'true'
  AND
  $__timeFilter("time")
group by time, data->'rtus'->'rtu-6'->'rtu-info'->'current_state'->>'ac'

This produces 1 as the answer and it should be a few hundred. I can’t seem to keep counting up during the time frame.

Have you checked out the Grafana query inspector and its correspondence with your standalone query?

Have you tested by removing the group by ‘ac’ attribute?