HARVEST_DATA

Device-submitted data records available through SORACOM Harvest Data.

Use HARVEST_DATA to query device-submitted data stored as structured JSON in CONTENT.

Public Contract

• Rows are limited to the current SORACOM Query request context.
• Rows are limited to the time range selected for the SORACOM Query request.
• The request time range is applied to TIMESTAMP.

Data Freshness

Updated near real time.

Columns

Column	Type	Description
RESOURCE_ID	TEXT	Identifier of the resource that submitted the data.
FULL_RESOURCE_ID	TEXT	Resource identifier path associated with the record.
TIME_WITH_SUFFIX	TEXT	Timestamp key used to distinguish records with the same timestamp.
TIME_MS	NUMBER	Timestamp in milliseconds.
TIMESTAMP	TIMESTAMP_NTZ	When the data was recorded.
OPERATOR_ID	TEXT	Operator identifier associated with the row.
GROUP_ID	TEXT	Group identifier associated with the resource.
CATEGORY	TEXT	Data category.
CONTENT_TYPE	TEXT	MIME type of the content.
CONTENT	VARIANT	Device-submitted JSON payload.
RESOURCE_TYPE	TEXT	Type of resource that submitted the data.
SIM_ID	TEXT	SIM identifier associated with the resource when available.

JSON / VARIANT Fields

CONTENT contains the JSON payload submitted by the device. Field names and value types depend on the data sent by each device or application.

VARIANT field names are case-sensitive. For example, CONTENT:TMP and CONTENT:tmp read different fields. Cast values to the type you need in query output.

Example:

SELECT
  RESOURCE_ID,
  TIMESTAMP,
  CONTENT:temperature::NUMBER AS temperature
FROM HARVEST_DATA
WHERE CONTENT:temperature IS NOT NULL;

Common Queries

Recent records for each resource:

SELECT
  RESOURCE_ID,
  TIMESTAMP,
  CONTENT
FROM HARVEST_DATA
QUALIFY ROW_NUMBER() OVER (
  PARTITION BY RESOURCE_ID
  ORDER BY TIMESTAMP DESC
) = 1;

Subscriber-originated records with a numeric payload field:

SELECT
  SIM_ID,
  RESOURCE_ID,
  TIMESTAMP,
  CONTENT:temperature::NUMBER AS temperature
FROM HARVEST_DATA
WHERE RESOURCE_TYPE = 'subscriber'
  AND CONTENT:temperature IS NOT NULL;

Records grouped by category:

SELECT
  CATEGORY,
  COUNT(*) AS record_count,
  MAX(TIMESTAMP) AS latest_recorded_at
FROM HARVEST_DATA
GROUP BY CATEGORY
ORDER BY latest_recorded_at DESC;