Query multi-value data

Request path and method

Notes

• You must use different API endpoints to write single-value data and multi-value data. To write single-value data, you must use the /api/put API endpoint. To query the multi-value data that has been written, you must use the /api/mquery API endpoint. To query the single-value data that has been written, you must use the /api/query API endpoint.

Request parameters

Timestamp unit

The timestamp unit is second or millisecond. Time Series Database (TSDB) determines the unit based on the numeric value and the following rules:

• If the timestamp range is [4284768,9999999999], the unit is second. The corresponding time range is [1970-02-20 00:59:28, 2286-11-21 01:46:39.]
• If the timestamp range is [10000000000,9999999999999], the unit is millisecond. The corresponding time range is [1970-04-27 01:46:40.000, 2286-11-21 01:46:39.999.]
• If the timestamp range is (-∞,4284768) or (9999999999999,+∞), the timestamp range is invalid.Note: These rules apply to the following API endpoints: /api/put, /api/mput, the /api/query, and /api/mquery. The first two API endpoints are used to write data. The other two API endpoints are used to query data.

Query data that is reported at a single time point

TSDB allows you to query data that is reported a single time point. You can set the start time and end time to the same time point, for example, "start":1356998400 and "end":1356998400.

Subqueries in the JSON format

Note

1. The maximum number of fields for a query is 200. An example is provided as follows:

In this example, a query contains three subqueries. The first subquery contains 3 fields, the second subquery contains 2 fields, and the third subquery contains 6 fields. The total number of fields in this query is calculated as follows: Total fields = 3 + 2 + 6 = 11. You must make sure that the number of total fields cannot exceed 200.

1. If you specify both the tags and filters parameters, the parameter you specify at the latter position in the JSON-formatted data takes effect.

Field queries in the JSON format

Note: For more information about the limit, dpValue, downsample, tags, and filters parameters, see the following descriptions.

Sample request

This topic provides a sample request: POST/api/mquery The request body is described as follows:

{
    "start" : 1346846400,
    "end" :   1346846411,
    "msResolution" : true,
    "queries" : [
        {
            "metric" : "wind",
            "fields" : [
                {
                    "field" : "speed",
                    "aggregator" : "sum",
                    "downsample" : "2s-last",
                      "alias" : "speed_sum"
                },
                {
                    "field" : "*",
                    "aggregator" : "sum",
                    "downsample" : "2s-count"
                }
            ]
        }
    ]
}

Parameters: limit and offset

The limit and offset parameters are used when query results are paginated. The limit parameter specifies the maximum number of data points that are returned on each page for a subquery. The default value of the limit parameter is 0. The default value 0 indicates that no limits are placed on the number of returned data points. The offset parameter specifies the number of data points that you want to skip for a subquery. The default value of the offset parameter is 0. The default value 0 indicates that no data points are skipped.

Note: You cannot set the limit or offset parameter to a negative number. The settings of the limit and offset parameters apply to the paginated query results for the multi-value data. The settings do not apply to the query results of a specific field.

Example

If you want to obtain the data points whose rankings are 1001 to 1500, set the limit parameter to 500 and the offset parameter to 1000.

{
    "start" : 1346846400,
    "end" :   1346846411,
    "msResolution" : true,
    "queries" : [
        {
            "metric" : "wind",
            "fields" : [
                {
                    "field" : "*",
                    "aggregator" : "sum",
                    "downsample" : "2s-count"
                }
            ],
            "filters" : [
                {
                    "filter" : "IOTE_8859_0005|IOTE_8859_0004",
                    "tagk" : "sensor",
                    "type" : "literal_or"
                }
            ],
            "limit" : 500,
            "offset" : 1000
        }
    ]
}

Parameter: dpValue

The dpValue parameter specifies the limit for data values. You can specify this parameter to filter data points that are to be returned. Valid values are >, <, =, <=, >=, and != .Note that only the OR operator is supported for the dpValue parameter values that are specified for different fields. The AND operator is not supported.

Example

{
    "start" : 1346846400,
    "end" :   1346846411,
    "msResolution" : true,
    "queries" : [
        {
            "metric" : "wind",
            "fields" : [
                {
                    "field" : "level",
                    "aggregator" : "avg",
                    "downsample" : "2s-avg",
                    "dpValue" : ">=8.0"
                }
            ],
            "filters" : [
                {
                    "filter" : "IOTE_8859_0005|IOTE_8859_0004",
                    "tagk" : "sensor",
                    "type" : "literal_or"
                }
            ]
        }
    ]
}

Parameter: downsample

If you query data that covers an excessively long time range, data points are aggregated based on the user-specified settings of downsampling. The valid format for queries is described as follows:

<interval><units>-<aggregator>[-fill policy]

where,

• interval: specifies a numeric value, such as 5 or 60. The value 0all indicates that the data points in the time range are aggregated into a single value.
• units: the unit. s represents second, m represents minute, h represents hour, d represents day, n represents month, and y represents year.Note: You can downsample data based on a calendar time interval. To use the calendar time interval, you must add c to the end of the value for the units parameter. For example, 1dc indicates the 24-hour period from 00:00 of the current day to 00:00 of the next day.
• aggregator: the aggregation settings.The following table describes the operators that are used for downsampling.

Fill policies

You can specify fill policies to determine how to fill missing values with pre-defined values. During downsampling, all timelines are split based on a specified time interval, and data points in each time range are aggregated. If no value exists during a time range in the downsampling result, you can specify a fill policy to fill the missing value with a pre-defined value. An example is used to explain fill policies. In this example, the timestamps of a timeline after downsampling are t+0, t+20, and t+30. If you do not specify a fill policy, only three values are reported. If you set the fill policy to null, four values are reported. The missing value at the time point t+10 is filled with null. The following table lists the fill policies, and the values that are used to fill missing values.

Fill policy (fixed value)

To fill missing values with a fixed value, you can add the fixed value to the end of the number sign (#). You can specify the fixed value as a positive or negative number. The valid format is described as follows:

<interval><units>-<aggregator>-fixed#<number>

Two examples are 1h-sum-fixed#6 and 1h-avg-fixed#-8.

Downsampling examples

Three downsampling examples are 1m-avg, 1h-sum-zero, and 1h-sum-near.

Note: The downsample parameter is optional for field queries. You can set this parameter to null or leave this parameter empty: {“downsample”: null} or {“downsample”: “”}. In this case, data is not downsampled. If you specify the downsample parameter for a field in a subquery, you must specify the downsample parameter for the other fields in the subquery. You must also use the same downsampling interval for the other fields in the subquery.

Parameter: aggregator

After downsampling, values along multiple timelines are obtained and timestamps of these timelines are aligned. You can perform aggregation to merge these timelines into one by aggregating the values at each aligned timestamp. Aggregation is not performed if only one timeline exists. During aggregation, each timeline must have a value at each aligned timestamp. If no value can be found at an aligned timestamp, interpolation is performed. For more information, see the following “Interpolation” section.Note: In field queries, the aggregator parameter is required. You can set this parameter to none, which indicates that data is not aggregated. In the TSDB multi-value data model, aggregation is either performed for all the fields or none of the fields for a query.

Interpolation

If a timeline has no value at a timestamp, a value is interpolated to this timeline at the timestamp. This occurs only when you do not specify a fill policy, and one of the other timelines to be aggregated has a value at this timestamp.

An example is used to explain interpolation. In this example, you want to merge two timelines by using the sum operator. The downsampling and aggregation settings are {“downsample”: “10s-avg”, “aggregator”: “sum”}. After downsampling is performed, the timestamps where values can be found are provided as follows:

Timeline 1: t+0, t+10, t+20, t+30
Timeline 2: t+0, t+20, t+30

Along timeline 2, the value at the t+10 timestamp is missing. Before aggregation, a value is interpolated for timeline 2 at this timestamp. The interpolation method varies based on aggregation operators. The following table lists the operators and interpolation methods.

Parameter: filters

You can specify filtering conditions by using the following methods:

• Specify the tag key.
  • tagk = *: You can perform a GROUP BY operation on the tag values of a tag key to aggregate the same tag values.
  • tagk = tagv1|tagv2: You can aggregate the tagv1 and tagv2 values of the tag key, respectively.
• Use the JSON format.

Filter types

Sample requests

Sample request with filters specified

The request body is described as follows:

{
    "start" : 1346846400,
    "end" :   1346846411,
    "msResolution" : true,
    "queries" : [
        {
            "metric" : "wind",
            "fields" : [
                {
                    "field" : "speed",
                    "aggregator" : "none",
                    "alias" : "column_speed"
                },
                {
                      "field" : "*",
                    "aggregator" : "none",
                    "alias" : "column_"
                }
            ],
            "filters" : [
                {
                    "filter" : "IOTE_8859_0005|IOTE_8859_0004",
                    "tagk" : "sensor",
                    "type" : "literal_or"
                }
            ]
        }
    ]
}

Query result

If a query is successful, the HTTP status code is 200 and the response is returned in the JSON format. The following table describes the response parameters.

Sample responses

• Value of the aggregator parameter: none

[
   {
      "metric":"wind",
      "columns":[
         "timestamp",
         "column_speed",
         "column_description",
         "column_direction",
         "column_level",
         "column_speed"
      ],
      "tags":{
         "city":"hangzhou",
         "country":"china",
         "province":"zhejiang",
         "sensor":"IOTE_8859_0005"
      },
      "aggregatedTags":[],
      "values":[
         [ 1346846406000, null, "Fresh breeze", "East", 0.5, null ],
         [ 1346846407000, null, "Fresh breeze", "South", 1.5, null ]
   },
   {
      "metric":"wind",
      "columns":[
         "timestamp",
         "column_speed",
         "column_description",
         "column_direction",
         "column_level",
         "column_speed"
      ],
      "tags":{
         "city":"hangzhou",
         "country":"china",
         "province":"zhejiang",
         "sensor":"IOTE_8859_0004"
      },
      "aggregatedTags":[],
      "values":[
         [ 1346846400000, 40.4, "Fresh breeze", "East", 0.4, 40.4 ],
         [ 1346846401000, 41.4, "Fresh breeze", "South", 1.4, 41.4 ],
         [ 1346846402000, 42.4, "Fresh breeze", "West", 2.4, 42.4 ],
         [ 1346846403000, 43.4, "Fresh breeze", "North", 3.4,43.4 ]
   }
]

• Value of the aggregator parameter: avg (to query the average wind speed and wind level based on all sensors in Hangzhou)

[
  {
    "metric": "wind",
    "columns": [
      "timestamp",
      "avg_level",
      "avg_speed"
    ],
    "tags": {
      "city": "hangzhou"
    },
    "aggregatedTags": [
      "country",
      "province",
      "sensor"
    ],
    "values": [
      [1346846400000, 0.25, 40.25],
      [1346846401000, 1.25, 41.25],
      [1346846402000, 2.5, 42.5],
      [1346846411000, 5.5, null]
    ]
  }
]