TagoSQL snippets

-- Aggregations run on the server: one small result row per variable instead of
-- thousands of raw readings.
--   $1 = device id
--   $2 = start of the time window, ISO 8601
SELECT variable,
       COUNT(*) AS readings,
       MIN(value) AS min_value,
       MAX(value) AS max_value,
       AVG(value) AS avg_value
FROM device($1) AS d
WHERE time > $2
GROUP BY variable
ORDER BY variable

-- devices() lists your device inventory (metadata, not stored data). Useful to
-- find silent devices: sort by last_input to see which stopped reporting.
-- devices_tag('key','value') is the same table restricted to one tag.
SELECT id, name, active, type, last_input
FROM devices() AS d
WHERE active = true
ORDER BY last_input DESC
LIMIT 100

-- Positional parameters ($1, $2, ...) keep values out of the query text: store the
-- query once and send different values on each execution.
--   $1 = device id        (example: "6a033c5b0528f6000c2ac5ee")
--   $2 = variable name    (example: "temperature")
--   $3 = start of the time window, ISO 8601 (example: "2026-07-01T00:00:00Z")
SELECT variable, value, unit, time
FROM device($1) AS d
WHERE variable = $2
  AND time > $3
ORDER BY time DESC
LIMIT 100

-- device_data_by_tag('key','value') is the fleet function: one row per active device
-- carrying the tag, each with that device's newest reading matching your filters.
-- A variable filter and a time lower bound are required; up to 5 tag pairs can be
-- listed (AND-combined). For fleets above your plan's device cap, page with the
-- after_device field of the execute request body.
--   $1 = start of the time window, ISO 8601 (devices silent since then are skipped)
SELECT device, device_name, variable, value, unit, time
FROM device_data_by_tag('type', 'sensor') AS f
WHERE variable = 'temperature'
  AND time > $1
ORDER BY device

-- JOINs combine data from multiple devices in one result (Starter plan or above).
-- Here a sensor's temperature is paired with an actuator's state recorded at the
-- same instant. Replace SENSOR_ID and ACTUATOR_ID with your device ids.
SELECT a.time,
       a.value AS temperature,
       b.value AS actuator_state
FROM device('SENSOR_ID') AS a
JOIN device('ACTUATOR_ID') AS b ON a.time = b.time
WHERE a.variable = 'temperature'
ORDER BY a.time DESC
LIMIT 50

-- The simplest TagoSQL query: the last 10 readings of a device, newest first.
-- device('id') reads the time-series data stored on that device.
-- Replace DEVICE_ID with a device id from your profile.
SELECT variable, value, unit, time
FROM device('DEVICE_ID') AS d
ORDER BY time DESC
LIMIT 10

-- session_user_tag('key') is filled by the server with the tag value of the user
-- executing the query: tag your devices and your Run users with the same key
-- (customer=acme on both) and every user sees only their fleet. The value never
-- comes from the request, and a user without the tag gets an empty result.
-- The COALESCE fallback is standard SQL and applies only when the profile owner
-- runs the query while authoring; for a signed-in user it never fires.
--   $1 = start of the time window, ISO 8601
SELECT device, device_name, variable, value, unit, time
FROM device_data_by_tag('customer', COALESCE(session_user_tag('customer'), 'acme')) AS f
WHERE variable = 'temperature'
  AND time > $1
ORDER BY device

-- session_user_id() takes no arguments: the server fills it with the id of the
-- Run user executing the query. Tag each user's device as owner=<run user id>
-- and this one stored query serves every user with only their own readings.
-- The COALESCE fallback is standard SQL and applies only when the profile owner
-- runs the query while authoring; a user with no matching device gets an empty
-- result.
--   $1 = start of the time window, ISO 8601
SELECT variable, value, unit, time
FROM device_tag('owner', COALESCE(session_user_id(), '6a4ff908be1ad0000bf49a2d')) AS d
WHERE time > $1
ORDER BY time DESC
LIMIT 50