Example Query API calls (simple)

The SimpleQuery object describes a simple SQL-like query comprising either raw or grouped data. You can view the full schema for this object in the Query API link given in the View Designer.

These examples can be run against the Bond Prices demo after opening and appending “/api/query/v1” to the web address of Omniscope Mobile.

Raw table query

Retrieves raw data, with optional filter, sort and range. Somewhat like an SQL SELECT ... query. The filters are applied on the input data, before retrieving the listed fields. The sort and range are applied on the result data, after retrieving the listed fields.

You might use this to create a paged table of underlying data values.

SimpleQuery:

{
    "filter": {
        "inputField": "Category",
        "value":      "Subordinated"
    },
    "fields": [ 
        "Category", "Ticker", "Issue Name"
    ],
    "sorts": [
        {
            "inputField": "Ticker",
            "direction":  "ASCENDING"
        }
    ],
    "range": {
        "start": 0,
        "length": 10
    }
}

SQL equivalent:

    SELECT Category, Ticker, Issue Name 
    FROM table
    WHERE Category="Subordinated"
    ORDER BY "Ticker" ASC
    OFFSET 0 LIMIT 10

Output

{
  "schema": {
    "fields": [
      {
        "name": "Category",
        "type": "TEXT"
      },
      {
        "name": "Ticker",
        "type": "TEXT"
      },
      {
        "name": "Issue Name",
        "type": "TEXT"
      }
    ]
  },
  "records": [
    [
      "Subordinated",
      "ABBEY",
      "SPI FINANCE PLC"
    ],
    [
      "Subordinated",
      "ABBEY",
      "ABBEY NATIONAL PLC"
    ],
    [
      "Subordinated",
      "ABBEY",
      "SCOTTISH MUTUAL ASSURANC"
    ],
    ...
  ],
  "diagnostics": {
    "reason": "Table",
    "execTime": "0.021s",
    "conversionTime": "0.001s",
    "cols": 3,
    "rows": 10
  }
}

See the tutorial on how to create a raw table query api based custom view.

Grouped table query

Retrieves rolled-up aggregated/grouped data, with optional filter, sort and range. The filters are applied on the input data, before aggregation/grouping. The sort and range are applied on the result data, after aggregation/grouping.

Somewhat like an SQL SELECT ... GROUP BY ... query.

SimpleQuery:

{
    "filter": {
        "inputField": "Category",
        "operator":   "IN",
        "value":      ["Subordinated", "Senior Unsecured"]
    },
    "groupings": [
        { 
            inputField: "Category",
            name: "Category" 
        }
    ], 
    "measures": [
        { 
            "inputField": "Coupon", 
            "function":   "MEAN" 
        },
        { 
            "function": "RECORD_COUNT" 
        }
    ],
    "sorts": [
        {
            "inputField": "Category",
            "direction":  "DESCENDING"
        }
    ],
    "range": {
        "start": 0,
        "length": 10
    }
}
 

SQL equivalent:

SELECT Category, AVG(Coupon), COUNT(*) 
FROM table
WHERE Category IN ("Subordinated", "Senior Unsecured")
GROUP BY Category
ORDER BY "Category" DESC
OFFSET 0 LIMIT 10

Output

In either case, the output is the same - a table of row/column data “records” (indexed as result.records[row][column]), and “schema” describing the columns (fields).

{
  "schema": {
    "fields": [
      {
        "name": "Category",
        "type": "TEXT"
      },
      {
        "name": "Coupon (MEAN)",
        "type": "NUMBER"
      },
      {
        "name": "RECORD_COUNT",
        "type": "NUMBER"
      }
    ]
  },
  "records": [
    [ "Subordinated",     7.055276041666667, 192 ],
    [ "Senior Unsecured", 6.199888127853881, 438 ]
  ]
}

Further reading

Next tutorial: Example Query API calls (advanced)