Get table rows

GET /hubdb/api/v2/tables/:tableId/rows

Method Details

HTTP Methods:

GET

Response Format:

json

Requires Authentication?

No

Rate Limited?

Yes

Headers

User-Agent

Products:

Marketing

Required Scope:

hubdb

Get the rows for a specific HubDB table. The results can be filtered and ordered using the options detailed below.

You can get the draft version of the table by including /draft at the end of the request path. See the example for details. Note: You must include authentication when requesting the draft version.

Required Parameters How to use Description
Hub/Portal ID portalId={Hub ID}
Used in the request URL
The Hub ID that the table belongs to.
Table Id {tableId}
Used in the request URL
The ID of the table that you're looking for.

Optional Parameters How to use Description
Get Draft details /draft
Used in the request URL
Include /draft at the end of the request path to get the draft details of the table.
Limit limit={number}
Used in the request URL
The maximum number of rows to return in the response. Defaults to 1000.
Offset offset={number}
Used in the request URL
Used to page through the results if the number of rows in the table is higher than the limit parameter.
Order By orderBy={column name}
Used in the request URL
Return the rows in the natural order of the specified column. You can reverse the sort by adding a - to the column name: orderBy=-bar. You can include this parameter multiple times to sort by multiple columns.
In addition to ordering by a column, there are three functions that can be used:
  • geo_distance(location_column_name, lattitude, longitude)
    Takes the name of a location column and coordinates, returns the rows ordered by how far away the value of the specified location column are from the provided coordinates.
  • length(column_name)
    Takes the name of a column, returns the rows ordered by the length of the column value (calculated as a string)
  • random()
    Returns the rows in random order.
These functions also support reverse ordering:
orderBy=-geo_distance(location_column,42.37,-71.07)
returns the items that are the farthest away first.

Filtering

In addition to the above parameters, you can filter the results by specific data. Filters are applied as query parameters, by adding the column name, followed by two underscores (_), and then the operator. For example, if you have a number column named 'bar', you can filter the results to only include rows where the 'bar' column is greater than 10 using this parameter: &bar__gt=10. Any number of filters can be included as query parameters in the request URL. All filters are ANDed together. ORing filters is not currently supported.

Operators can only be applied to specific column types. When passing values for MULTISELECT columns, the ids or names should be separated by commas (e.g. multiselect_column__contains=1,2) For the purposes of these filters, the built in column hs_id is a NUMBER column, the hs_created_at column is a DATETIME, and the hs_path column is a TEXT column.

For DATETIME filters, you can use relative dates in place of timestamps in order to specify a value relative to the current time. For example, -3h would correspond to the timestamp 3 hours before now, whereas 10s would correspond to 10 seconds in the future. Supported timeunits are ms (milliseconds), s (seconds), m (minutes), h (hours), d (days). Current time can be used by specifying a zero value: 0s

Filter Name Operator Works with
Equals eq (or none) All column types
This filter is applied if no operator is used
&example_column=value
When used with MULTISELECT columns, returns rows that exactly match supplied values
Not Equal ne All column types
Contains contains TEXT, RICHTEXT, MULTISELECT
When used with MULTISELECT columns, returns rows that contain at least one of the supplied values.
Less Than lt NUMBER, DATE, DATETIME
Less Than or Equal lte NUMBER, DATE, DATETIME
Greater Than gt NUMBER, DATE, DATETIME
Greater Than or Equal gte NUMBER, DATE, DATETIME
Null is_null All column types, except BOOLEAN
This filter does not require a value (&example_column__is_null=)
Not Null not_null All column types, except BOOLEAN
This filter does not require a value (&example_column__not_null=)
Like like TEXT, RICHTEXT
Not Like not_like TEXT, RICHTEXT
Contains (case insensitive) icontains TEXT, RICHTEXT
Starts With startswith TEXT, RICHTEXT
In in MULTISELECT
Returns rows where the column includes one of the passed options