Example Query API calls (advanced)

These examples omit filter, sorts, and range, for brevity. They can be run against the Bond Prices demo.

Roll up to 1 record and retrieve the record count

Supply an empty groupings array (or omit groupings entirely), and only provide one measure.

SimpleQuery:

{
    "groupings": [ ], 
    "measures":  [ { "function": "RECORD_COUNT" } ]
}

SQL equivalent:

SELECT COUNT(*) 
FROM table

Output:

{
  "schema": {
    "fields": [
      { "name": "RECORD_COUNT", "type": "NUMBER" }
    ]
  },
  "records": [
    [ 892 ]
  ]
}

Roll up to 1 record and retrieve value range for some fields

Supply an empty groupings array (or omit groupings entirely), and only provide one measure.

SimpleQuery:

{
    "measures":  [ 
        { "inputField": "Issue Date", "function": "MIN" }, 
        { "inputField": "Issue Date", "function": "MAX" },
        { "inputField": "Coupon", "function": "MIN" }, 
        { "inputField": "Coupon", "function": "MAX" } 
    ]
}

SQL equivalent:

SELECT MIN(Issue_Date), MAX(Issue_Date), MIN(Coupon), MAX(Coupon) 
FROM table

Output:

{
  "schema": {
    "fields": [
      {
        "name": "Issue Date (MIN)",
        "type": "DATE"
      },
      {
        "name": "Issue Date (MAX)",
        "type": "DATE"
      },
      {
        "name": "Coupon (MIN)",
        "type": "NUMBER"
      },
      {
        "name": "Coupon (MAX)",
        "type": "NUMBER"
      }
    ]
  },
  "records": [
    [ "1984-08-15T23:00:00Z", "2005-06-29T23:00:00Z", 0.5, 12.625 ]
  ]
}

^{Note that dates are always exchanged in JSON using strings with the ISO 8601 format or same but with time element omitted.}

Count records for category values

E.g. to plot a simple Bar chart.

SimpleQuery:

{
    "groupings": [ { "field": "Category" } ], 
    "measures":  [ { "function": "RECORD_COUNT" } ]
}

SQL equivalent:

SELECT Category, COUNT(*) 
FROM table
GROUP BY Category

Output:

{
  "schema": {
    "fields": [
      {
        "name": "Category",
        "type": "TEXT"
      },
      {
        "name": "RECORD_COUNT",
        "type": "NUMBER"
      }
    ]
  },
  "records": [
    [ "Collateralised",   136 ],
    [ "Senior Unsecured", 438 ],
    [ "Subordinated",     192 ],
    [ "Supranational",    126 ]
  ]
}

Find combinations of two fields

Use 2 groupings and empty or missing measures. E.g. to create a 2 tier tree.

SimpleQuery:

{
    "groupings": [ 
        { "field": "Category" }, 
        { "field": "Is Split" }
    ]
}

SQL equivalent:

SELECT Category, Is_Split 
FROM table
GROUP BY Category, Is_Split

Output:

{
  "schema": {
    "fields": [
      {
        "name": "Category",
        "type": "TEXT"
      },
      {
        "name": "Is Split",
        "type": "TEXT"
      }
    ]
  },
  "records": [
    [ "Collateralised",   "N" ],
    [ "Collateralised",   "Y" ],
    [ "Senior Unsecured", "N" ],
    [ "Senior Unsecured", "Y" ],
    [ "Subordinated",     "N" ],
    [ "Subordinated",     "Y" ],
    [ "Supranational",    "N" ],
    [ "Supranational",    "Y" ]
  ]
}

Find unique value count for a field

A nested inner query obtains the unique values. The outer query counts the rows. This gives a 1x1 cell table containing the unique value count.

Query:

{
  "@visokiotype": "QueryApi.AggregateQuery",
  input: {
    "@visokiotype": "QueryApi.AggregateQuery",
    "groupings": [ { "inputField": "Composite Rating" } ]
  },
  "groupings": [],
  "measures": [ { "function": "RECORD_COUNT" } ]
}

Find unique value counts for a field, split by another field

The above query yields an overall unique value count. If you want to split this by a field, here’s the variant. This yields a 2 column table, first typically being a bar view’s split labels, second being the bar view’s measure value.

Query:

{
  "@visokiotype": "QueryApi.AggregateQuery",
  input: {
    "@visokiotype": "QueryApi.AggregateQuery",
    "groupings": [ 
      { "inputField": "Composite Rating" }, 
      { "inputField": "Category" } 
    ]
  },
  "groupings": [
    { "inputField": "Category" } 
  ],
  "measures": [ 
    { "function": "RECORD_COUNT", "name": "Unique ratings count" } 
  ]
}