Executing SuiteQL Queries Through REST Web Services

SuiteQL is a query language based on the SQL database query language. SuiteQL provides advanced dynamic query capabilities that can be used to access NetSuite records.

For more information about SuiteQL in general, see SuiteQL.

You can execute SuiteQL queries through REST web services by sending a POST request to the suiteql resource, and specifying the query in the request body after the body parameter q.

In the request URL you can also specify the number of results you want to return in a single page and the page offset. For information about paging and offset values, see Collection Paging.

You can use the Records Catalog to get information about all record types and fields that are available through the analytics data source. The browser includes a page for each record type with all available fields and joined record types, if applicable. For each field, you can also find whether that field is available for use in SuiteAnalytics Workbook, SuiteScript, SuiteTalk REST web services, and SuiteAnalytics Connect. For more information, see Records Catalog Overview.

Using SuiteQL queries, you can return a maximum of 100,000 results. If you expect your queries to return more than 100,000 results, you can use SuiteAnalytics Connect with the NetSuite2.com data source. For more information about how to use SuiteAnalytics Connect, see Getting Started with SuiteAnalytics Connect.

For more information, see query.runSuiteQLPaged(options).

The following example shows a SuiteQL query executed through REST web services. Note that Prefer: transient is a required header parameter.

            POST https://demo123.suitetalk.api.netsuite.com/services/rest/query/v1/suiteql?limit=10&offset=10
  Prefer: transient
  {
    "q": "SELECT email, COUNT(*) as count FROM transaction GROUP BY email"
  }

The following is a shortened response.

          {
  "links": ...,
  "count": 3,
  "offset": 10,
  "totalResults": 53,
  "items": [
    {
       "links": [],
       "email": "test@netsuite.com",
       "count": "143"
    },
    {
       "links": [],
       "email": "test2@netsuite.com",
       "count": "144"
    },
    {
       "links": [],
       "email": "test3@netsuite.com",
       "count": "145"
    }
  ],
  ...
}

In SuiteQL queries, you can also use advanced SQL functions, for example joins and concatenations. The following example shows a request where the first and last names from employee records are concatenated as full name.

          {
      "q": "SELECT CONCAT(firstname,lastname) as fullname FROM employee"
}

When working with SuiteQL, consider the limitation listed at SuiteQL Limitations and Exceptions.

Related Topics