> ## Documentation Index
> Fetch the complete documentation index at: https://developers.hubspot.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

---
id: a7d1137e-0dfe-4490-9a69-60943ecac724
---

# Use HubSQL to query HubSpot data (BETA)

> Use the HubSQL endpoint to retrieve data using SQL queries.

export const RequiredIndicator = () => {
  return <span className="required-indicator">
      required
    </span>;
};

export const BetaDisclaimerBanner = () => <Warning>
        This functionality is currently in beta. By participating in this beta, you agree to HubSpot's <a href="https://legal.hubspot.com/developer-terms">Developer Terms</a> and <a href="https://legal.hubspot.com/developerbetaterms">Developer Beta Terms</a>. Note that the functionality is still under active development and is subject to change based on testing and feedback.
    </Warning>;

<Warning>
  This functionality is currently in private beta. Access to this beta can be [requested](https://app.hubspot.com/l/product-updates/?rollout=298929) in your HubSpot account.

  By participating in this beta, you agree to HubSpot's <a href="https://legal.hubspot.com/developer-terms">Developer Terms</a> and <a href="https://legal.hubspot.com/developerbetaterms">Developer Beta Terms</a>. Note that the functionality is still under active development and is subject to change based on testing and feedback.
</Warning>

HubSQL is a query layer for HubSpot CRM data. Instead of calling individual object-specific REST endpoints, you provide a query in the request body to a single HubSQL endpoint. The result is a flat JSON array of rows representing the matched records.

## Making requests

To retrieve data using HubSQL, make a `POST` request to `/analytics/hubsql/2027-03-beta/query`:

* Include your SQL query as a string in the `query` parameter in the request body.
* You can optionally include a `pageSize` parameter to specify the number of entries returned in each page of results (note that if `pageSize` is omitted, the response will default to a page size of 10 entries).

For example, the following request body would retrieve the names and amounts for deals greater than \$1000:

```json theme={null}
{
  "query": "SELECT dealname, amount FROM OBJECT.DEAL WHERE amount > 1000",
  "pageSize": 5
}
```

Note that the `query` parameter cannot be left empty.

The response includes up to three fields, detailed in the table below:

| Field     | Type    | Description                                                                                                                                                                                                                 |
| --------- | ------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `total`   | Integer | The total rows returned from your query.                                                                                                                                                                                    |
| `results` | Array   | The array of records matching your query, representing SQL-style rows as JSON. All property types requested in your query will be returned as strings. If a requested property has no value, it will be returned as `null`. |
| `paging`  | Object  | An object that contains a nested `next.after` field for cursor-based pagination. Consult the [pagination section](#pagination) below for more details on how to page through results.                                       |

The code block below demonstrates an example response:

```json theme={null}
{
  "total": 14,
  "results": [
    { "amount": "15000", "dealname": "big deal" },
    { "amount": "4250", "dealname": "Globex renewal" },
    { "amount": "5", "dealname": "2026 deal" },
    { "amount": null, "dealname": "yet another 2023 deal" },
    { "amount": null, "dealname": "Jorb K - New Deal" }
  ],
  "paging": {
    "next": {
      "after": "eyJjdXJzb3IiOiIyMDAifQ=="
    }
  }
}
```

### Authentication

Both [OAuth](/docs/apps/developer-platform/build-apps/authentication/overview#oauth) and [static auth](/docs/apps/developer-platform/build-apps/authentication/overview#static-auth) tokens are supported, along with [service keys](/docs/apps/developer-platform/build-apps/authentication/account-service-keys). Depending on the authorization type, [property access permissions](https://knowledge.hubspot.com/properties/restrict-view-edit-access-for-properties) configured in your account may be enforced:

* **OAuth:** property access permissions are enforced. If a user doesn't have view access to a property, querying that property returns `403 INSUFFICIENT_PERMISSIONS` with a message listing the restricted properties.
* **Static auth and service keys:** property access permissions are <u>not</u> enforced. All properties are queryable regardless of per-user restrictions.

### Scopes

When retrieving CRM object data, your app must be authorized with the read scope that corresponds to each object type your query references. For example, to query `OBJECT.DEAL`, you'd need to authorize the `crm.objects.deals.read` scope.

To query for events, the `business-intelligence` scope is required.

Consult the [scopes reference article](/docs/apps/developer-platform/build-apps/authentication/scopes) for all available scopes.

### Pagination

HubSQL supports cursor-based pagination via the `after` and `pageSize` fields you can include in your request:

* The `after` property, also known as the "cursor", is a Base64 URL-safe encoded token.
* The `pageSize` property is an optional query parameter that controls the number of rows returned per page, up to a maximum of 100 rows.
  * If `pageSize` is omitted, it will default to 10 rows.
  * If the `pageSize` exceeds the `LIMIT` provided in your `query`, the effective page size is capped at the `LIMIT` value.
* [Aggregation](#aggregate-functions) queries (`GROUP BY`, `COUNT`, `SUM`, etc.) do not support pagination.

The recommended pagination flow would be as follows:

1. Send your initial request with a `query` and optional `pageSize` in the request body. Do not include `after` in this initial request.
2. Check if the response includes `paging.next.after`, which indicates that additional results are available.
3. Send another request with the same `query` and `pageSize` in the request body, but include an additional `after` property set to the corresponding cursor value from the previous response.
4. Repeat steps 2 and 3 until the response no longer includes `paging.next.after`.

The code block below demonstrates this pagination flow:

```javascript expandable theme={null}
// First request
{
  "query": "SELECT dealname, amount FROM OBJECT.DEAL ORDER BY createdate DESC",
  "pageSize": 100
}

// First response (has more results)
{
  "total": 150,
  "results": [ ... 100 rows ... ],
  "paging": { "next": { "after": "eyJjdXJzb3IiOiIxMDAifQ==" } }
}

// Second request (using cursor from first response)
{
  "query": "SELECT dealname, amount FROM OBJECT.DEAL ORDER BY createdate DESC",
  "pageSize": 100,
  "after": "eyJjdXJzb3IiOiIxMDAifQ=="
}

// Second response (no more results — paging omitted)
{
  "total": 150,
  "results": [ ... 50 rows ... ]
}
```

## Data sources

All HubSQL queries must include a data source, which indicates the data type and associated properties you're requesting. The syntax and supported data sources are detailed in the sections below.

### Data source resolution

Data sources are specified using the SQL-style format `{SCHEMA}.{TABLE}`.

* Currently, `OBJECT` and `EVENT` are the only supported values for `SCHEMA`.
* If `SCHEMA` is omitted, the query will default to `OBJECT` as the schema.

#### OBJECT

Use `OBJECT.{TABLE}` to query for CRM data, where `TABLE` corresponds to the object type you want to retrieve, such as `DEAL`.

The `TABLE` can be provided in two different formats:

* Fully-qualified name (FQN): the singular name of an object type in your account (e.g., `DEAL`, `CONTACT`, etc).
* Object type ID: the `objectTypeId` of an object, provided within quotes (e.g., `"0-1"` for contacts, `"0-3"` for deals, `"2-12345"` for a custom object with an `objectTypeId` of 12345). For example, `OBJECT."0-1"` would be the full data source you'd need to include for querying contact data. Refer to [this list of all object type ID values](/docs/api-reference/latest/crm/understanding-the-crm#object-type-ids).

#### EVENT

Use `EVENT.{TABLE}` to query for event data, where `TABLE` corresponds to the event type, such as `e_ad_interaction`.

The `TABLE` can be provided in two different formats:

* Fully-qualified name (FQN): the singular name of an event type in your account (e.g., `EVENT.e_ad_interaction`, `EVENT.pe123_my_custom_event`, etc).
* Event type ID: the numeric type ID of an event provided within backticks.
  * For example, `` `EVENT.`4-1553675` `` would correspond to `e_ad_interaction`. For an account-specific custom event, the identifier string would resemble: `` `EVENT.`6-1234567` ``.
  * You can also query for app events using backticks or double-quotes, but they may need to be escaped using backslashes. For example, `EVENT.\"ae1158877_integrators-timeline-event-type-id-12672\"`.

Learn more about events in the [events API guide](/docs/api-reference/latest/events/guide), or on the [HubSpot Knowledge Base](https://knowledge.hubspot.com/reports/create-custom-events).

### Supported data sources and property types

The following standard CRM objects are currently supported as data sources during this phase of the beta:

```text expandable theme={null}
APPOINTMENT
CALL
CAMPAIGN
CART
COMMERCE_PAYMENT
COMMUNICATION
COMPANY
CONTACT
CONTRACT
COURSE
DEAL
DEAL_SPLIT
DISCOUNT
EMAIL
ENGAGEMENT
FEE
FEEDBACK_SUBMISSION
FORECAST
GOAL_TARGET
INVOICE
LEAD
LINE_ITEM
LISTING
MEETING_EVENT
NOTE
ORDER
PARTNER_ACCOUNT
PARTNER_CLIENT
PARTNER_SERVICE
POSTAL_MAIL
PRODUCT
PROJECT
QUOTE
QUOTE_TEMPLATE
SERVICE
SUBSCRIPTION
TASK
TAX
TICKET
USER
```

In addition, account-specific object types are also supported:

* [App objects](/docs/apps/developer-platform/add-features/app-objects/overview) are supported using the format `1-{ObjectTypeId}`
* [Custom objects](/docs/api-reference/latest/crm/objects/custom-objects/guide) are supported using the format `2-{ObjectTypeId}`

You can make cross-object queries using the `LEFT JOIN` [clause](#join).

<Warning>
  [Sensitive Data](/docs/api-reference/latest/crm/properties/sensitive-data) is not supported via the HubSQL endpoint.
</Warning>

All standard and custom events are supported when querying for `EVENT`, as well as [app events](/docs/apps/developer-platform/add-features/app-events/overview).

If you attempt to query an unsupported data source, you'll receive a `DATA_SOURCE_NOT_SUPPORTED` error in the response.

## Properties

Properties are requested as SQL-style columns in your query.

* Property types for a specific object can be retrieved and managed using the [properties API](/docs/api-reference/latest/crm/properties/guide#retrieve-properties).
* Event property types can be retrieved using the [events API](/docs/api-reference/latest/events/guide).

### Querying for properties

Object or event properties in your query can be specified in a fully qualified format (e.g., `DEAL.dealname`).

Bare names (e.g., `dealname`) are supported for properties referenced by the `FROM` clause, but cannot be used for any associated objects referenced by the `JOIN` [clause](#join).

### Property type response formats

All property types you request in your query are returned as strings. The table below details how each property type is serialized:

| Type                                    | Format                                                                                                                                                                      | Example                        |
| --------------------------------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | ------------------------------ |
| `string`, `enumeration`, `bool`, `json` | Raw value                                                                                                                                                                   | `"closedwon", "true"`          |
| `datetime`                              | ISO 8601 UTC string                                                                                                                                                         | `"2024-01-15T13:30:00.000Z"`   |
| `date`                                  | `YYYY-MM-DD` (in UTC)                                                                                                                                                       | `"2024-01-15"`                 |
| `number`                                | Up to 8 significant decimal places. Additional significant figures will be rounded to the nearest neighbor (e.g., 0.5 will be rounded up). Trailing zeros will be stripped. | `"15000", "1.5", "1.23456789"` |

If a requested property type is not populated, it will be returned as `null`.

## Syntax

HubSQL supports standard SQL syntax, with some limitations. Each of the supported clauses are listed in the sections below, along with any associated caveats to keep in mind as you write your queries.

### SELECT

Provide [property type names](#properties), [functions](#functions), or aliases as columns in your query.

You can use `SELECT *` in your query, but the rows in the resulting response will only include the `hs_object_id` property. Usage of `SELECT *` is only supported for objects, and cannot be used when querying for event data.

<Warning>
  **Please note:** all `EVENT` queries must have at least one [aggregation function](#aggregate-functions) to be valid.
</Warning>

<Note>
  Arithmetic expressions (e.g., `amount * 1.1`) and usage of `CASE`/`WHEN` are not currently supported.
</Note>

### FROM

Specify the [data source](#data-sources) to retrieve CRM data from, using the `SCHEMA.TABLE` format.

You can only retrieve data from one table at a time.

<Note>
  Referencing multiple tables (e.g., `FROM OBJECT.DEAL, `OBJECT.CONTACT\`), writing subqueries, or including table aliases are not currently supported.
</Note>

### JOIN

Combine records from two `OBJECT` tables based on their CRM associations using the `LEFT JOIN` clause.

For example, the following query would return deal names with their associated contacts, including their first name and their email address:

```sql theme={null}
SELECT dealname, CONTACT.firstname, CONTACT.email
  FROM OBJECT.DEAL
  LEFT JOIN OBJECT.CONTACT
```

Note that the example above doesn't include an `ON` clause, which will result in the default association being used. To specify a specific association path, check out the [`ON` section](#on-clause) below.

The following restrictions apply to joining tables:
**JOIN type:** only `LEFT JOIN` is currently supported. Using `INNER JOIN`, `RIGHT JOIN`, `FULL JOIN`, and `CROSS JOIN` will be rejected.
**Schema:** both tables must use the `OBJECT` schema. `EVENT` tables cannot be joined.
**Self-joins:** each table may appear at most once in the combined query.
**Subqueries in JOIN:** only plain table references are supported.

#### Column qualification

Unqualified column names (e.g. `dealname`) resolve against the primary table. Columns from the joined table must be qualified with the table's FQN or an alias:

```sql theme={null}
SELECT dealname, amount, CONTACT.firstname, CONTACT.email, COMPANY.name 
    FROM OBJECT.DEAL 
    LEFT JOIN OBJECT.CONTACT 
    LEFT JOIN OBJECT.COMPANY 
    WHERE DEAL.amount > 1000
```

#### ON clause

The `ON` clause specifies which association path to traverse:

| Form                         | Association type                                      |
| ---------------------------- | ----------------------------------------------------- |
| `ON 'ASSOCIATION_TYPE_NAME'` | Named CRM association type (e.g. `'CONTACT_TO_DEAL'`) |
| `ON '0-N'`                   | Numeric combined association type ID                  |

Check out the examples in the tabs below for guidance on using named associations or numeric combined association type ID:

<Tabs>
  <Tab title="Named association">
    ```sql theme={null}
    SELECT dealname, CONTACT.firstname
      FROM OBJECT.DEAL
      LEFT JOIN OBJECT.CONTACT ON 'CONTACT_TO_DEAL'
    ```
  </Tab>

  <Tab title="Numeric combined association type ID">
    ```sql theme={null}
    SELECT dealname, CONTACT.firstname
      FROM OBJECT.DEAL
      LEFT JOIN OBJECT.CONTACT ON '0-4'
    ```
  </Tab>
</Tabs>

Standard SQL column-comparison predicates (`ON d.id = c.id`) are not supported. Only string literals will be accepted.

### WHERE

Filter on object or event properties. The [property type](#querying-for-properties) must precede the operator of your `WHERE` clause.

The supported operators are detailed in the table below.

| Operator                   | Example                                                        |
| -------------------------- | -------------------------------------------------------------- |
| `=,` `!=`, `<>`            | `dealstage` = 'closedwon'                                      |
| `<,` `<=`, `>,` `>=`       | `amount >= 1000`                                               |
| `IN (...)`, `NOT IN (...)` | `dealstage IN ('closedwon', 'qualifiedtobuy')`                 |
| `BETWEEN ... AND ...`      | `amount BETWEEN 1000 AND 5000`                                 |
| `NOT BETWEEN ... AND ...`  | `amount NOT BETWEEN 1000 AND 5000`                             |
| `LIKE 'pattern'`           | `dealname LIKE '%corp%'` <br /> <br /> `dealname LIKE ‘corp%’` |
| `NOT LIKE 'pattern'`       | `dealname NOT LIKE '%corp%'`                                   |
| `IS NULL`, `IS NOT NULL`   | `closedate IS NULL`                                            |

Keep the following caveats in mind when using the `WHERE` clause:

* If nesting multiple `WHERE` clauses, you can include up to five `OR` groupings (i.e., a filter group), with a maximum of 18 total individual filters.
* Do not include `= NULL`, and instead opt for the `IS NULL` operator.
* Using `LIKE` with a leading wildcard is not supported (e.g., `dealname LIKE ‘%corp’`).
* When using `NOT LIKE`, you should only use the "contains" format: `%pattern%`  is supported. The prefix form, `NOT LIKE 'Acme%'` will be rejected.
* When using the `BETWEEN` operator, both bounds must be the same type (e.g., two numbers, or two dates in `YYYY-MM-DD` format). Mixing types throws an error.
* `NOT BETWEEN` internally expands to two `OR` conditions. Because `OR` can't sit inside `AND`, `NOT BETWEEN` can only appear at the top level of the `WHERE` clause or directly under a top-level `OR`. For example, the following `WHERE` clause is <u>not</u> supported:

```sql theme={null}
WHERE amount NOT BETWEEN 1000 AND 5000 AND dealstage = 'closedwon'
```

* While you can use `NOT` on a single predicate (e.g., `NOT dealstage = 'closedlost'` is equivalent to using `!=`), usage of `NOT` with compound conditions is not supported. For example, the `WHERE` clause below cannot be used, and should instead be rewritten by negating each condition individually using positive operators:

```sql theme={null}
WHERE NOT (dealstage = 'closedwon' AND amount > 1000)
```

### Using logical operators

When using logical operators, the `WHERE` clause must be in disjunctive normative form: `OR` cannot be nested inside an `AND` group, and should instead be rewritten to have a top-level `OR` that consist of one (or more) `AND` groups.

For example, all of the following `WHERE` clauses are valid:

```sql theme={null}
WHERE amount > 1000 AND dealstage = 'closedwon'
WHERE amount > 1000 OR dealstage = 'closedwon'
WHERE (amount > 1000 AND dealstage = 'closedwon')
   OR (amount > 5000 AND dealstage = 'qualifiedtobuy')
```

However, the `WHERE` clause below is <u>invalid</u>:

```sql theme={null}
WHERE (dealstage = 'closedwon' OR dealstage = 'qualifiedtobuy') AND amount > 1000
```

The invalid statement above could be rewritten to use `IN`:

```sql theme={null}
WHERE dealstage IN ('closedwon', 'qualifiedtobuy') AND amount > 1000
```

### Filtering EVENT data

When querying for `EVENT` data, keep the following caveats in mind when filtering based on dates:

* To filter events based on the date they occurred, use the `occurredAt` property.
  * If you omit an `occurredAt` filter, the query will default to retrieve the last 7 days of event data.
  * The maximum `occurredAt` span is 90 days.
  * The end of the range is capped at the end of the current day.
  * `occurredAt` filters cannot be combined with `OR`. Instead, use `AND` to constrain the time range.
* Relative date filters, such as `PREVIOUS_PERIOD` or `CURRENT_PERIOD`, as well as usage of `DATE_TRUNC`, are only supported when using the `occurredAt` event property. As an alternative, you can use date literals instead.

### GROUP BY

Group matching records that have the same values into summary rows.

For example, the following query would group matching deals by their deal stage:

```sql theme={null}
SELECT dealstage, COUNT(*), SUM(amount)
FROM OBJECT.DEAL
GROUP BY dealstage
```

The following rules apply to using `GROUP BY`:

* Use bare [property names](#querying-for-properties) as the column to a `GROUP_BY` clause. Column aliases are also allowed.
* Every non-aggregate column in SELECT must appear in GROUP BY (and vice-versa).
* At most 2 GROUP BY columns.
* `HAVING` is not supported, and any conditions should instead be moved to a `WHERE` clause.

### ORDER BY

Sorts matching records in a specific order. Available values are `ASC` (ascending order, which is the default), or `DESC` (descending order).

The behavior of `ORDER BY` depends on whether you're using an aggregate or non-aggregate query:

* **Aggregate:** when using any `GROUP BY` or aggregate function, `ORDER BY` must reference a `SELECT` alias. Your query is limited to a maximum of 1 `ORDER BY` property.

```sql theme={null}
SELECT dealstage, COUNT(*) AS deal_count FROM OBJECT.DEAL
GROUP BY dealstage
ORDER BY deal_count DESC
```

* **Non-aggregate:** any valid property name can be referenced. Your query can include up to 100 `ORDER BY` properties.

```sql theme={null}
ORDER BY createdate DESC
```

The following restrictions apply when using `ORDER BY` with `EVENT` queries:

* **Search queries:** only the `occurredAt` property is supported. If `ORDER BY` is omitted from your query, search results default to ascending by the `occurredAt` property.
* **Aggregate queries:** a maximum of one sort column is allowed (metric or dimension).

### LIMIT

Restrict the number of matched records.

* If omitted, the default maximum is 10 rows (or the custom value you specified using the `pageSize` [request body parameter](#making-requests)).
* The maximum `LIMIT` value is 10,000 rows for queries without an aggregation function. When your query includes a aggregate function, the maximum `LIMIT` is 500 rows. Note that the `LIMIT` is distinct from the `pageSize` parameter used for [pagination](#pagination), which has a separate maximum of 100 rows per page.

### Identifiers and literals

Values included in your query are case-insensitive. Any identifiers containing hyphens should be wrapped in double-quotes (e.g., `"my-custom-property"`).

The conventions below apply based the identifier type:

* **String literals** use single quotes ('closedwon').
* **Date and datetime literals:** use quoted strings in `YYYY-MM-DD` or `YYYY-MM-DDTHH:MM:SSZ formats` ( '2024-01-01' and  '2024-05-17T00:00:00Z')
* **Booleans:** use lower-cased, unquoted boolean literals (`true` or `false`).
* **Numerics:** both integers or decimals are allowed (`1000`, `3.14`).

## Functions

HubSQL supports aggregate, scalar, and filter functions.

### Aggregate functions

The table below outlines the supported aggregate functions and their supported properties. Note that for `EVENT` queries, you cannot use `occurredAt` for any aggregate function.

| Function                    | Supported properties |
| --------------------------- | -------------------- |
| `COUNT(*) or COUNT(column)` | any                  |
| `COUNT(DISTINCT column)`    | any                  |
| `SUM(column)`               | number, datetime     |
| `AVG(column)`               | number, datetime     |
| `MIN(column)`               | number, datetime     |
| `MAX(column)`               | number, datetime     |
| `MEDIAN(column)`            | number, datetime     |

<Note>
  The `COUNT(DISTINCT column)` function is approximate. The `COUNT(DISTINCT column)` and `MEDIAN(column)` aggregate functions cannot be used with `GROUP BY`.
</Note>

Using a function on an incompatible property type will return an error that resemble the following:

```json theme={null}
{
  "status": "error",
  "category": "VALIDATION_ERROR",
  "subCategory": "HubSqlApiError.INVALID_QUERY",
  "message": "SUM does not support string properties ('dealname'). Supported property types: number, datetime.",
  "errors": [
    { "message": "SUM does not support string properties ('dealname'). Supported property types: number, datetime." }
  ]
}
```

Property names in the response are auto-generated for aggregations. The table below shows the mapping between each aggregate function the corresponding auto-generated key:

| Function         | Allowed property types | Auto-generated key |
| ---------------- | ---------------------- | ------------------ |
| `COUNT(*)`       | any                    | `count`            |
| `COUNT(column)`  | any                    | `count_column`     |
| `SUM(amount)`    | `number`, `datetime`   | `sum_amount`       |
| `AVG(amount)`    | `number`, `datetime`   | `avg_amount`       |
| `MIN(amount)`    | `number`, `datetime`   | `min_amount`       |
| `MAX(amount)`    | `number`, `datetime`   | `max_amount`       |
| `MEDIAN(amount)` | `number`, `datetime`   | `median_amount`    |

The resulting value in the response follows the format: `{auto-generated-key}_{property_name}` (e.g., `sum_amount`).

For example, the following query would provide a ranked list of deal stages from most deals to fewest, with the total pipeline value per stage:

```sql theme={null}
SELECT dealstage, COUNT(*), SUM(amount)
  FROM OBJECT.DEAL
  GROUP BY dealstage
  ORDER BY deal_count DESC
```

### Scalar functions

Currently, only the `DATE_TRUNC` function is supported in queries to reduce a date or timestamp based on the provided `time_unit`.

| Function     | Signature                         | Supported time units                                             |
| ------------ | --------------------------------- | ---------------------------------------------------------------- |
| `DATE_TRUNC` | `DATE_TRUNC(property, time_unit)` | `time_unit`: `'DAY'`, `'WEEK'`, `'MONTH'`, `'QUARTER'`, `'YEAR'` |

The following caveats apply to usage of the `DATE_TRUNC` function:

* Supported `WHERE` operators are: `=`, `\>`, `\>=`, `\<`, `\<=`, `BETWEEN`, and `NOT BETWEEN`.
* A property can only be wrapped in `DATE_TRUNC` once per query.
* If `DATE_TRUNC(property, time_unit)` appears in `SELECT`, the `GROUP BY` must use the same granularity for that column (e.g. 'month' in `SELECT` requires 'month' in `GROUP BY`)

### Filter functions

The following functions can be used to filter matching records based on date or datetime properties. These functions must precede the `=` operator of a `WHERE` operator.

| Function                | Description                             |
| ----------------------- | --------------------------------------- |
| `CURRENT_PERIOD`        | The current day/week/month/quarter/year |
| `CURRENT_PERIOD_SO_FAR` | The current period up to now            |
| `PREVIOUS_PERIOD`       | The previous period, or last N periods  |
| `NEXT_PERIOD`           | The next period, or next N periods      |

Filter functions require date or datetime properties. Using them on an enumeration, string, or numeric property returns an error.

The time period boundary depends on the property type:

* **Date properties:** boundaries are calculated using the account's configured timezone.
* **Datetime properties:** boundaries are calculated in UTC.

## Limits

Usage of HubSQL is subject to limits on both query complexity and the rate at which you send queries in a given time period.

### Query limits

Exceeding any of these caps returns a `QUERY_TOO_COMPLEX` error with a specific message.

| Query clause or function                              | Limit  |
| ----------------------------------------------------- | ------ |
| Non-aggregated `SELECT` fields                        | 100    |
| Aggregated `SELECT` fields                            | 20     |
| `WHERE` filter conditions                             | 18     |
| Filter groups (top-level `OR`)                        | 5      |
| `GROUP` BY fields                                     | 2      |
| `ORDER` BY fields (non-aggregate)                     | 100    |
| `ORDER` BY fields (aggregate)                         | 1      |
| Maximum `LIMIT` value (without an aggregate function) | 10,000 |
| Maximum `LIMIT` value (with aggregate function)       | 500    |
| Maximum `JOIN` clauses                                | 3      |
| Cross-object filter conditions                        | 2      |

### Rate limits

The limits below are enforced per-account and per-app:

| Window     | Limit           |
| ---------- | --------------- |
| Per second | 1 request       |
| Per day    | 10,000 requests |

Exceeding a limit returns `429 RATE_LIMIT_EXCEEDED` with the policy name and a retry-after hint in the response body. For a `429`, use exponential backoff with jitter starting at 1 second. For a `500`, retry up to 3 times before contacting support.

## Errors

Errors will follow the standard HubSpot API response format:

```json theme={null}
{
  "status": "error",
  "category": "VALIDATION_ERROR",
  "subCategory": "HubSqlApiError.INVALID_PROPERTY",
  "message": "The query references unavailable properties. Remove or replace the listed properties and try again.",
  "errors": [
    { "message": "The following properties do not exist for DEAL[foo].", "in": "query" }
  ]
}
```

The possible `category` and `subCategory` values are detailed in the table below:

| `subCategory`         | HTTP                             | `category` | Description                                                                                                                      |
| --------------------- | -------------------------------- | ---------- | -------------------------------------------------------------------------------------------------------------------------------- |
| `VALIDATION_ERROR`    | `INVALID_QUERY`                  | 400        | Possible reasons include bad syntax, nonexistent properties, or otherwise unsupported SQL constructs.                            |
| `VALIDATION_ERROR`    | `INVALID_DATA_SOURCE`            | 400        | The `OBJECT.TABLE` reference can't be resolved.                                                                                  |
| `VALIDATION_ERROR`    | `INVALID_PROPERTY`               | 400        | One or more column names don't exist on the object type.                                                                         |
| `VALIDATION_ERROR`    | `DATA_SOURCE_NOT_SUPPORTED`      | 400        | Thrown if you provided a data source that isn't yet supported.                                                                   |
| `VALIDATION_ERROR`    | `QUERY_TOO_COMPLEX`              | 400        | A query limit was exceeded (see the [table](#query-limits) above).                                                               |
| `MISSING_PERMISSIONS` | `INSUFFICIENT_PERMISSIONS`       | 403        | Missing CRM read scope. If your app is using user-level access, this error indicates field-level permission denied for the user. |
| `RATE_LIMITS`         | `RATE_LIMIT_EXCEEDED`            | 429        | Rate limit exceeded. Check the retry-after hint.                                                                                 |
| `RATE_LIMITS`         | `JOIN_QUERY_RATE_LIMIT_EXCEEDED` | 429        | The daily limit for queries with JOINs has been reached. Check the retry-after hint.                                             |
| *(internal)*          | `INTERNAL_ERROR`                 | 500        | Server-side failure; retry up to 3x, then contact support with `correlationId`                                                   |