All Products
Search
Document Center

Query multi-value data

Last Updated: Sep 11, 2020

Request path and method

Request path Request method Description
/api/mquery POST Queries the data.

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

Parameter Type Required Description Default value Example
start Long Yes The start time. Unit: second or millisecond. For more information about how to determine the unit, see the Timestamp unit section. N/A 1499158925
end Long No The end time. Unit: second or millisecond. For more information about how to determine the unit, see the Timestamp unit section. The default value is the current time of the TSDB server. The current time of the TSDB server 1499162916
queries Array Yes The array of subqueries. N/A For more information, see the subquery description.
msResolution boolean No The array of subqueries. false This parameter is valid for queries only if the unit of the raw data is second. If this parameter is set to true, the unit of the timestamp in the query result is converted to milliseconds. Otherwise, the original time unit is retained. If the unit of the raw data is millisecond, the returned timestamp is always in milliseconds.

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

Parameter Type Required Description Default value Example
metric String Yes The metric name. N/A wind
fields List Yes The fields that you want to query. N/A N/A
limit Integer No The maximum number of data points to return on each page for a subquery. The query results are paginated. 0 1000
offset Integer No The number of the returned data points that you want to skip for a subquery. The query results are paginated. 0 500
tags Map No Specifies the tag condition based on which the data is filtered. This parameter is mutually exclusive with the filters parameter. N/A N/A
filters List No Specifies the filtering condition. This parameter is mutually exclusive with the tags parameter. N/A N/A

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

Parameter Type Required Description Default value Example
aggregator String Yes The aggregate function. For more information, see the “Parameter: aggregator” section. N/A sum
field String Yes The field name. You can use the asterisk (*) to query all the fields of the metric. N/A N/A
alias String No The alias of the field in the response. N/A N/A
downsample String No The downsampling of time series data. N/A 60m-avg
rate Boolean No Specifies whether to calculate the growth rate of the specified metric value. The formula is Vt - Vt-1/t1 - t-1. false true
dpValue String No The filtering conditions based which the data points are returned. Valid values: >, <, =, <=, >=, and != . N/A >=1000

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:

  1. {
  2. "start" : 1346846400,
  3. "end" : 1346846411,
  4. "msResolution" : true,
  5. "queries" : [
  6. {
  7. "metric" : "wind",
  8. "fields" : [
  9. {
  10. "field" : "speed",
  11. "aggregator" : "sum",
  12. "downsample" : "2s-last",
  13. "alias" : "speed_sum"
  14. },
  15. {
  16. "field" : "*",
  17. "aggregator" : "sum",
  18. "downsample" : "2s-count"
  19. }
  20. ]
  21. }
  22. ]
  23. }

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.

  1. {
  2. "start" : 1346846400,
  3. "end" : 1346846411,
  4. "msResolution" : true,
  5. "queries" : [
  6. {
  7. "metric" : "wind",
  8. "fields" : [
  9. {
  10. "field" : "*",
  11. "aggregator" : "sum",
  12. "downsample" : "2s-count"
  13. }
  14. ],
  15. "filters" : [
  16. {
  17. "filter" : "IOTE_8859_0005|IOTE_8859_0004",
  18. "tagk" : "sensor",
  19. "type" : "literal_or"
  20. }
  21. ],
  22. "limit" : 500,
  23. "offset" : 1000
  24. }
  25. ]
  26. }

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

  1. {
  2. "start" : 1346846400,
  3. "end" : 1346846411,
  4. "msResolution" : true,
  5. "queries" : [
  6. {
  7. "metric" : "wind",
  8. "fields" : [
  9. {
  10. "field" : "level",
  11. "aggregator" : "avg",
  12. "downsample" : "2s-avg",
  13. "dpValue" : ">=8.0"
  14. }
  15. ],
  16. "filters" : [
  17. {
  18. "filter" : "IOTE_8859_0005|IOTE_8859_0004",
  19. "tagk" : "sensor",
  20. "type" : "literal_or"
  21. }
  22. ]
  23. }
  24. ]
  25. }

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:

  1. <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.
Operator Description
avg Returns the average value.
count Returns the number of data points.
first Returns the first value.
last Returns the last value.
min Returns the minimum value.
max Returns the maximum value.
sum Returns the sum of values.
zimsum Returns the sum of values.

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 Fill missing values with
none N/A (No value is filled by default.)
nan NaN
null null
zero 0
linear The value that is calculated based on linear interpolation
previous The previous value
near The adjacent value
after The next value
fixed A user-specified fixed value (See the following example.)

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:

  1. <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.

Operator Description Interpolation method
avg Returns the average value. Linear interpolation is used. Linear interpolation is performed based on linear slopes.
count Returns the number of data points. The value 0 is interpolated.
mimmin Returns the minimum value. The maximum value is interpolated.
mimmax Returns the maximum value. The minimum value is interpolated.
min Returns the minimum value. Linear interpolation is used.
max Returns the maximum value. Linear interpolation is used.
none Skips data aggregation. The value 0 is interpolated.
sum Returns the sum of values. Linear interpolation is used.
zimsum Returns the sum of values. The value 0 is interpolated.

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.

    
Parameter    
    
Type    
    
Required    
    
Description    
    
Default value    
    
Example    
   
type   
   
String   
   
Yes   
   
The filter type. For more information,   see the following description.   
   
N/A   
   
literal_or   
   
tagk   
   
String   
   
Yes   
   
The name of the tag key.   
   
N/A   
   
host   
   
filter   
   
String   
   
Yes   
   
The filter expression.   
   
N/A   
   
web01|web02   
   
groupBy   
   
Boolean   
   
No   
   
Specifies whether to perform a GROUP   BY operation on the tag values.   
   
false   
   
false   

Filter types

    
Filter type    
    
Example    
    
Description    
   
literal_or   
   
web01| web02   
   
Aggregation is performed on multiple   tag values, respectively. During the aggregation, case sensitivity is   implemented.   
   
wildcard   
   
*mysite.com   
   
Aggregation is performed on the tag   values that contain the specified wildcard, respectively. During the   aggregation, case sensitivity is implemented.   

Sample requests

Sample request with filters specified

The request body is described as follows:

  1. {
  2. "start" : 1346846400,
  3. "end" : 1346846411,
  4. "msResolution" : true,
  5. "queries" : [
  6. {
  7. "metric" : "wind",
  8. "fields" : [
  9. {
  10. "field" : "speed",
  11. "aggregator" : "none",
  12. "alias" : "column_speed"
  13. },
  14. {
  15. "field" : "*",
  16. "aggregator" : "none",
  17. "alias" : "column_"
  18. }
  19. ],
  20. "filters" : [
  21. {
  22. "filter" : "IOTE_8859_0005|IOTE_8859_0004",
  23. "tagk" : "sensor",
  24. "type" : "literal_or"
  25. }
  26. ]
  27. }
  28. ]
  29. }

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.

Parameter Description
metric The metric name.
columns The column names that were returned in the query result.
tags The tags whose values were not aggregated.
aggregateTags The tags whose values were aggregated.
values The query result of the tuple type.

Sample responses

  • Value of the aggregator parameter: none
  1. [
  2. {
  3. "metric":"wind",
  4. "columns":[
  5. "timestamp",
  6. "column_speed",
  7. "column_description",
  8. "column_direction",
  9. "column_level",
  10. "column_speed"
  11. ],
  12. "tags":{
  13. "city":"hangzhou",
  14. "country":"china",
  15. "province":"zhejiang",
  16. "sensor":"IOTE_8859_0005"
  17. },
  18. "aggregatedTags":[],
  19. "values":[
  20. [ 1346846406000, null, "Fresh breeze", "East", 0.5, null ],
  21. [ 1346846407000, null, "Fresh breeze", "South", 1.5, null ]
  22. },
  23. {
  24. "metric":"wind",
  25. "columns":[
  26. "timestamp",
  27. "column_speed",
  28. "column_description",
  29. "column_direction",
  30. "column_level",
  31. "column_speed"
  32. ],
  33. "tags":{
  34. "city":"hangzhou",
  35. "country":"china",
  36. "province":"zhejiang",
  37. "sensor":"IOTE_8859_0004"
  38. },
  39. "aggregatedTags":[],
  40. "values":[
  41. [ 1346846400000, 40.4, "Fresh breeze", "East", 0.4, 40.4 ],
  42. [ 1346846401000, 41.4, "Fresh breeze", "South", 1.4, 41.4 ],
  43. [ 1346846402000, 42.4, "Fresh breeze", "West", 2.4, 42.4 ],
  44. [ 1346846403000, 43.4, "Fresh breeze", "North", 3.4,43.4 ]
  45. }
  46. ]
  • Value of the aggregator parameter: avg (to query the average wind speed and wind level based on all sensors in Hangzhou)
  1. [
  2. {
  3. "metric": "wind",
  4. "columns": [
  5. "timestamp",
  6. "avg_level",
  7. "avg_speed"
  8. ],
  9. "tags": {
  10. "city": "hangzhou"
  11. },
  12. "aggregatedTags": [
  13. "country",
  14. "province",
  15. "sensor"
  16. ],
  17. "values": [
  18. [1346846400000, 0.25, 40.25],
  19. [1346846401000, 1.25, 41.25],
  20. [1346846402000, 2.5, 42.5],
  21. [1346846411000, 5.5, null]
  22. ]
  23. }
  24. ]