TagoSQL snippets

-- Aggregations run on the server: one small result row per variable instead of
-- thousands of raw readings.
--   $1 = device id (example: "6a033c5b0528f6000c2ac5ee")
--   $2 = start of the time window, ISO 8601 (example: "2026-01-01T00:00:00Z")
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

-- The params column returns one json element per device parameter, ordered by key,
-- in the same shape as the REST params API: { id, key, value, sent }. A device with
-- no parameters returns an empty array. The element id is the parameter row id, so
-- the values you read here can be edited or deleted through the REST params
-- endpoints. params is selectable only: it carries no comparison semantics, so it
-- cannot appear in WHERE, GROUP BY, ORDER BY, or inside an aggregate.
SELECT id, name, params
FROM devices() AS d
WHERE d.active = true
LIMIT 100

-- 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.
-- To read each device's configuration parameters, see device-configuration-params.
SELECT id, name, active, type, network, last_input
FROM devices() AS d
WHERE active = true
ORDER BY last_input DESC
LIMIT 100

-- devices_tag('key','value') is the device inventory restricted to devices carrying
-- that tag. Sorting by last_input ascending puts the silent devices first, which is
-- the quickest way to audit a group of sensors. params comes back as the device's
-- configuration array, so you can check firmware or mode alongside activity.
SELECT id, name, active, last_input, params
FROM devices_tag('device_type', 'sensor') AS d
ORDER BY last_input ASC
LIMIT 100

-- entities() lists your entities (metadata, not their rows), which is how you find
-- the id to pass to entity('ENTITY_ID'). entities_tag('key','value') is the same
-- table restricted to entities carrying one tag.
SELECT id, name, tags, updated_at
FROM entities() AS e
ORDER BY updated_at DESC
LIMIT 100

-- entity('ENTITY_ID') reads the rows of one entity you own. Its columns are the
-- entity's own schema, returned with their native types, so SELECT * is the safe
-- starting point; you can only reference columns the entity actually defines.
-- Call GET /sql/tables?entity_id=<id> to discover those columns.
SELECT *
FROM entity('ENTITY_ID') AS e
ORDER BY created_at DESC
LIMIT 50

-- 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-01-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 (example: "2026-01-01T00:00:00Z")
--        devices silent since then are skipped
SELECT device, device_name, variable, value, unit, time
FROM device_data_by_tag('device_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 humidity 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 humidity,
       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 = 'humidity'
ORDER BY a.time DESC
LIMIT 50

-- The simplest TagoSQL query: the last 10 readings of a device, newest first.
-- device('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 (example: "2026-01-01T00:00:00Z")
SELECT device, device_name, variable, value, unit, time
FROM device_data_by_tag('customer', COALESCE(session_user_tag('customer'), 'acme')) AS f
WHERE variable = 'battery_level'
  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. Replace RUN_USER_ID with a Run user id from your profile.
--   $1 = start of the time window, ISO 8601 (example: "2026-01-01T00:00:00Z")
SELECT variable, value, unit, time
FROM device_tag('owner', COALESCE(session_user_id(), 'RUN_USER_ID')) AS d
WHERE time > $1
ORDER BY time DESC
LIMIT 50

-- device_tag('key','value') resolves to the FIRST device in your profile carrying
-- the tag, deterministic by device id, and reads its stored data. Use it for tags
-- that identify exactly one device (one gateway per site, for example). When
-- several devices share the tag, only one is read: use device('DEVICE_ID') for a
-- specific device, or device_data_by_tag to fan out across all of them.
SELECT variable, value, unit, time
FROM device_tag('device_type', 'gateway') AS d
ORDER BY time DESC
LIMIT 50