Query

The Query object

A Query object represents a series of database commands executing on a Data Source.

A Query object has the following identifiers:

• id : string - The unique identifier of a query

A Query object is created by executing a command on a Data Source. For more information, see: Data Source - Execute data source command

Cancel query

Cancels an executing query.

Arguments:

• query - The identifier of the query to be retrieved

Response object:

This method returns an object with no properties.

Example request:

$ curl \
    -H "Accept: application/vnd.jackdb.v2+json" \
    -u "${JACKDB_API_KEY_ID}@api:${JACKDB_API_KEY_SECRET}" \
    -X POST \
    "${JACKDB_API_URL}/query/31cf58d7-b0d7-49e4-99b9-1b47eb922dc2/cancel"

Example response:

{}

Delete query

Deletes a query.

Arguments:

• query - The identifier of the query to be retrieved

Response object:

This method returns an object with no properties.

Example request:

$ curl \
    -H "Accept: application/vnd.jackdb.v2+json" \
    -u "${JACKDB_API_KEY_ID}@api:${JACKDB_API_KEY_SECRET}" \
    -X DELETE \
    "${JACKDB_API_URL}/query/31cf58d7-b0d7-49e4-99b9-1b47eb922dc2"

Example response:

{}

NOTE: Queries with a non-zero retention period are automatically deleted once the retention period has expired. Users will not need to manually delete queries unless they want to explicitly delete sooner than the expiration.

Retrieve a query

Retrieves the basic details of an existing query.

Arguments:

• query - The identifier of the query to be retrieved

Response object:

• A Query object with the following properties:
  • id - string : The unique identifier of the query
  • createdAt - timestamp : When the query was created
  • startedAt - timestamp | null : When the query started executing
  • completedAt - timestamp | null : When the query completed
  • success - boolean | null : Whether the query completed successfully or null if it is still in progress
  • numResults - number | null : Number of results or null if no results have been generated yet

Example request:

$ curl \
    -H "Accept: application/vnd.jackdb.v2+json" \
    -u "${JACKDB_API_KEY_ID}@api:${JACKDB_API_KEY_SECRET}" \
    "${JACKDB_API_URL}/query/31cf58d7-b0d7-49e4-99b9-1b47eb922dc2"

Example response:

{
  "id": "31cf58d7-b0d7-49e4-99b9-1b47eb922dc2",
  "createdAt": "2022-08-06T21:09:29.100Z",
  "startedAt": "2022-08-06T21:09:29.112Z",
  "completedAt": "2022-08-06T21:09:32.325Z",
  "success": true,
  "numResults": 3
}

Note: The response returned by this endpoing will change over the lifecyle of the query until has completed.

Retrieve a query result

Retrieves the result of an existing query.

Arguments:

• query - The identifier of the query to be retrieved
• resultIndex - The zero-origin index of the query result

Response object:

• A Query Result object with the following properties:
  • queryId - string : The unique identifier of the query
  • ordinalPosition - string : The zero-origin index of the query result
  • type - string : The type of query result
  • startedAt - timestamp : When this query result started
  • completedAt - timestamp | null : When this query result completed or null if it is not yet complete
  • detail - object : The detail for this query result

Example request:

$ curl \
    -H "Accept: application/vnd.jackdb.v2+json" \
    -u "${JACKDB_API_KEY_ID}@api:${JACKDB_API_KEY_SECRET}" \
    "${JACKDB_API_URL}/query/31cf58d7-b0d7-49e4-99b9-1b47eb922dc2/result/0"

Example response:

{
  "queryId": "31cf58d7-b0d7-49e4-99b9-1b47eb922dc2",
  "ordinalPosition": 0,
  "type": "query",
  "startedAt": "2022-08-06T21:09:29.112Z",
  "completedAt": "2022-08-06T21:09:32.325Z",
  "detail": {
    "columns": [
      {
        "ordinalPosition": 0,
        "name": "my_column",
        "type": "varchar",
        "format": "string"
      },
      {
        "ordinalPosition": 1,
        "name": "other_column",
        "type": "int",
        "format": "integer"
      }
    ],
    "hasMore": false,
    "numRows": 100
  }
}

Note: The detail field in the response returned by this endpoint will vary depending on the type of the query result.

Result Types:

• error - An error result with an error field with the specific error message.
• query - A result set of rows. The detail field includes a columns attribute describing the shape of the result set.
• update - A DML result with an optional updateCount field indicating the number of rows that were modified.

Error Detail Object:

• error - string - The error that occurred while executing the command.

Query Detail Object:

• columns - array - The shape of the result set, an array of objects with the fields:
  • ordinalPosition - number - The zero-origin index of the result column.
  • name - string - The name of the result column.
  • type - string - The data type of the column as reported by the database.
  • format - string - The data format the column result data as it will appear serialized in the result rows response.
• hasMore - boolean - Whether the result is still fetching additional rows or has completed.
• numRows - number - Number of result rows available.
• error - string - Error message that occurred while processing a result set. (optional)

Update Detail Object:

• updateCount - number | null - The number of rows that were modified by a DML command.

Retrieve rows from a query result

Retrieves result rows of an existing query result.

Arguments:

• query - The identifier of the query to be retrieved
• resultIndex - The zero-origin index of the query result
• seek - The zero-origin start index of the result rows to fetch (optional)
• limit - The number of result rows to fetch (optional)

Response object:

• An array of result rows. Each row is itself an array of UTF-8 encoded strings or null.

Example requests:

Fetch default limit of rows from the beginning of the result:

$ curl \
    -H "Accept: application/vnd.jackdb.v2+json" \
    -u "${JACKDB_API_KEY_ID}@api:${JACKDB_API_KEY_SECRET}" \
    "${JACKDB_API_URL}/query/31cf58d7-b0d7-49e4-99b9-1b47eb922dc2/result/0/rows"

Fetch default limit of rows starting from the 500th row:

$ curl \
    -H "Accept: application/vnd.jackdb.v2+json" \
    -u "${JACKDB_API_KEY_ID}@api:${JACKDB_API_KEY_SECRET}" \
    "${JACKDB_API_URL}/query/31cf58d7-b0d7-49e4-99b9-1b47eb922dc2/result/0/rows?seek=500"

Fetch 100 rows starting from the 251st row (the seek argument is zero origin):

$ curl \
    -H "Accept: application/vnd.jackdb.v2+json" \
    -u "${JACKDB_API_KEY_ID}@api:${JACKDB_API_KEY_SECRET}" \
    "${JACKDB_API_URL}/query/31cf58d7-b0d7-49e4-99b9-1b47eb922dc2/result/0/rows?seek=500&limit=100"

Example response:

[
  ["alice", "123", "2022-08-01"],
  ["bob", "456", null],
  ["carl", "789", "2022-08-01"]
]

The default limit is one-thousand (1000) rows. To fetch all rows of a larger result set use multiple requests with an explicit seek value to paginate the response.

Column Data Formats

Each entry in an array of row values is serialized according to the data format describe in the query result response:

• string - A textual string, e.g. "testing".
• integer - An integer value serialized as a base-10 string, e.g. "123".
• numeric - A numeric value serialized as a base-10 string with an optinal decimal separator for the fractional part, e.g. "123.456".
• date - A date serialized as an ISO-8601 string in the YYYY-MM-DD format, e.g. "2015-01-17".
• timestamp - A timestamp without timezone value serialized as an ISO-8601 string in the YYYY-MM-DDTHH:MM:SS.FFF format, e.g. "2015-01-17T12:33:52Z".
• timestamptz - A timestamp with timezone value serialized as an ISO-8601 string in the YYYY-MM-DDTHH:MM:SS.FFFZ format, e.g. "2015-01-17T12:33:52Z".
• bytea - A binary value serialized as a hex-encoded string, e.g. "4a61636b4442206973207468652062657374"