Create results

The /api/v1/create-results endpoint on the self hosted query engine, accepts POST HTTP requests containing Vizzly formatted queries, and returns results in the Vizzly format.

When would I use this?

If you have Vizzly webhooks set up, you will start receiving real-time and scheduled events to your endpoint. One such example is a scheduled report event, which contains all the information needed to run a query against the query engine. Then, you can package the result in any format you like such as a CSV or a PDF and send this to your users.

Implementation details

The request body should match the following type definition;

{
  queries: Array<Query>;
  virtualFields: {
    [dataSetId: string]: Array<VirtualField>;
  };
  identityConfigIntegritySignature: string;
}

Identity config integrity signature

This is a data access token, that is one of the tokens returned by your identity callback.

Query

export namespace Query {
  // A dimension can be thought of as a
  // field to "group by" in a SQL statement.
  export type Dimension = Field;
 
  // Field ID provided in your data set.
  export type Field = string;
 
  export type Limit = number;
 
  export type Offset = number;
 
  export type Aggregate =
    | "countDistinct"
    | "count"
    | "mean"
    | "sum"
    | "min"
    | "max"
    | "none";
 
  export type Measure = {
    field: Field;
    aggregate: Aggregate;
  };
 
  export type Order = {
    field: Field;
    direction: "asc" | "desc";
    aggregate: Aggregate;
  };
 
  export type Operator =
    | ">"
    | "<"
    | "="
    | '!='
    | ">="
    | "<="
    | "is_one_of"
    | "is_not_one_of"
    | "starts_with"
    | "ends_with"
    | "contains_substring"
    | "does_not_contain_substring";
 
  export type Filter = {
    field: Field;
    op: Operator;
    value: any;
  };
 
  export namespace TimeDimension {
    export type Truncate =
      | "second"
      | "minute"
      | "hour"
      | "day"
      | "month"
      | "year";
  }
 
  export type TimeDimension = {
    field: Field;
    truncate: TimeDimension.Truncate;
  };
}
 
export type Query = {
  dataSetId: string;
  measure: Array<Query.Measure>;
  dimension: Array<Query.Dimension>;
  timeDimension: Query.TimeDimension | null;
 
  /*
    The following filter structure
    [
      [<< filter 1>>, << filter 2 >>],
      [<< filter 3>>, << filter 4 >>],
    ]
    would be read as;
    accept data records where (filter 1 and filter 2 are truthy), or (filter 3 and filter 4 are truthy)
  */
  filter: Array<Array<Query.Filter>>;
  order: Array<Query.Order>;
  limit?: Query.Limit;
  offset?: Query.Offset;
};

Virtual Fields

The structure of virtual fields is complex and in a state of flux. Therefore we will send the exact value to be used here in webhook events, which you can pass through un-touched to the query engine.

Response

The response, if successful, will contain a list of results, which you can use to build CSVs, PDFs, .xlsx files, or send to Google Sheets using their API (opens in a new tab).

export namespace Result {
  export type RowOfValues = Array<string | number | Date | null>;
  export type Field = {
    // ID of the field, matching the ID of the field in the data set.
    id: string;
 
    // Name of the field your users will see
    publicName: string;
 
    // What data type is the field before the aggregation?
    // For example, if the field holds a `count` aggregation of
    // `string` fields, then the dataType here would be `string`.
    dataType: "number" | "boolean" | "string" | "date_time";
  };
}
 
export type Result = {
  fields: Array<Result.Field>;
  content: Array<Result.RowOfValues>;
};

Last updated on January 27, 2023

Performance Custom