Use HubSQL to query HubSpot data (BETA)

This functionality is currently in private beta. Access to this beta can be requested in your HubSpot account.By participating in this beta, you agree to HubSpot’s Developer Terms and Developer Beta Terms. Note that the functionality is still under active development and is subject to change based on testing and feedback.

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:

{
  "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 below for more details on how to page through results.

The code block below demonstrates an example response:

{
  "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 and static auth tokens are supported, along with service keys. Depending on the authorization type, property access permissions 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 not 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 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 queries (GROUP BY, COUNT, SUM, etc.) do not support pagination.

The recommended pagination flow would be as follows:

Send your initial request with a query and optional pageSize in the request body. Do not include after in this initial request.
Check if the response includes paging.next.after, which indicates that additional results are available.
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.
Repeat steps 2 and 3 until the response no longer includes paging.next.after.

The code block below demonstrates this pagination flow:

// 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.

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, or on the HubSpot Knowledge Base.

Supported data sources and property types

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

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 are supported using the format 1-{ObjectTypeId}
Custom objects are supported using the format 2-{ObjectTypeId}

You can make cross-object queries using the LEFT JOIN clause.

Sensitive Data is not supported via the HubSQL endpoint.

All standard and custom events are supported when querying for EVENT, as well as app events. 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.
Event property types can be retrieved using the events API.

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.

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, 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.

Please note: all EVENT queries must have at least one aggregation function to be valid.

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

FROM

Specify the data source to retrieve CRM data from, using the SCHEMA.TABLE format. You can only retrieve data from one table at a time.

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

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:

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 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:

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:

Named association
Numeric combined association type ID

SELECT dealname, CONTACT.firstname
  FROM OBJECT.DEAL
  LEFT JOIN OBJECT.CONTACT ON 'CONTACT_TO_DEAL'

SELECT dealname, CONTACT.firstname
  FROM OBJECT.DEAL
  LEFT JOIN OBJECT.CONTACT ON '0-4'

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 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%'` `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 not supported:

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:

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:

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 invalid:

WHERE (dealstage = 'closedwon' OR dealstage = 'qualifiedtobuy') AND amount > 1000

The invalid statement above could be rewritten to use IN:

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:

SELECT dealstage, COUNT(*), SUM(amount)
FROM OBJECT.DEAL
GROUP BY dealstage

The following rules apply to using GROUP BY:

Use bare property names 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.

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.

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).
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, 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

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

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

{
  "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:

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:

{
  "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 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`

​Making requests

​Authentication

​Scopes

​Pagination

​Data sources

​Data source resolution

​OBJECT

​EVENT

​Supported data sources and property types

​Properties

​Querying for properties

​Property type response formats

​Syntax

​SELECT

​FROM

​JOIN

​Column qualification

​ON clause

​WHERE

​Using logical operators

​Filtering EVENT data

​GROUP BY

​ORDER BY

​LIMIT

​Identifiers and literals

​Functions

​Aggregate functions

​Scalar functions

​Filter functions

​Limits

​Query limits

​Rate limits

​Errors

Making requests

Authentication

Scopes

Pagination

Data sources

Data source resolution

OBJECT

EVENT

Supported data sources and property types

Properties

Querying for properties

Property type response formats

Syntax

SELECT

FROM

JOIN

Column qualification

ON clause

WHERE

Using logical operators

Filtering EVENT data

GROUP BY

ORDER BY

LIMIT

Identifiers and literals

Functions

Aggregate functions

Scalar functions

Filter functions

Limits

Query limits

Rate limits

Errors