# REST API v1 - Search data API endpoint

> For the complete documentation index, see [llms.txt](https://developers.thoughtspot.com/docs/llms.txt)

Source: https://developers.thoughtspot.com/docs/search-data-api-v1

# REST API v1 - Search data API endpoint

> **IMPORTANT:** The REST API v1 endpoints are deprecated and replaced with the REST API v2 endpoints. Your existing integrations with REST API v1 endpoints will continue to function without disruption until further notice. ThoughtSpot strongly recommends migrating your integrations to the REST API v2 endpoints.

## Supported operations

To construct a search query string and retrieve data from ThoughtSpot programmatically, use the `/tspublic/v1/searchdata` REST API.

 
| API endpoint | Available from |
| --- | --- |
| 
`[**POST** /tspublic/v1/searchdata]({{navprefix}}/{{search-data-api}}#search-data-api-ref)`  
Allows searching for data from a specific data source in the ThoughtSpot application.

 | 

ThoughtSpot Cloud ts7.april.cl  
ThoughtSpot Software 6.0.x

 |

## Required permissions

You must have view access to the data source objects to run a search query.

## Search query

When issuing a query through the ThoughtSpot UI, users make selections to disambiguate a query. It is often difficult to programmatically use the result set of a query that runs in the ThoughtSpot UI search bar. Because the selection is not possible with an API approach, the API query language is modified to include query disambiguation.

#### Components of a search query

In ThoughtSpot Query Language, the components of a query are classified into various types of tokens:

-   [Column](#Column)
    
-   [Operator](#Operator)
    
-   [Value](#Value)
    
-   [Date Bucket](#Date-Bucket)
    
-   [Keyword](#Keyword)
    
-   [Calendar](#Calendar)
    

Column

Columns must be enclosed in square brackets, \[ \].

For example, in the query revenue by ship mode, both revenue and ship mode are columns. A valid query for the API is:

\[revenue\] by \[ship mode\]

Operator

ThoughtSpot supports various operators such as =, !=, >, >=, <=, <, contains, not contains, and so on. Use these operators in the API query in the same manner as in the UI.

For example, specify revenue over 1000, and limit ship mode to 'air':

\[revenue\] > 1000 \[ship mode\] = 'air'

Value

String (text) and date values must be enclosed within quotes, ''. Do not use quotes for numeric values, except for dates.

When using multiple values, separate them by a comma.

For example, when a ThoughtSpot UI query is revenue top 2 ship mode, the equivalent API query is:

\[revenue\] top 2 \[ship mode\]

For example, when a ThoughtSpot UI query is revenue ship mode = air, the equivalent API query is:

\[revenue\] \[ship mode\] = 'air'

Date Bucket

In the ThoughtSpot UI, when there are several date columns, users can bind date bucket tokens to a specific column. When using the API, this binding between the date column and the date bucket must be made explicit. Use a period to separate the column with which the date bucket is bound, and the date bucket token.

Single word date buckets can be expressed as is. Multi-word date buckets must be enclosed within quotes.

For example, when a ThoughtSpot UI query is `revenue commit date monthly`, and if monthly is bound to commit date, the equivalent API query is:

\[revenue\] \[commit date\].monthly

When a ThoughtSpot UI query is `revenue day of week = 5`, and if day of week is bound to commit date, the equivalent API query is as follows:

\[revenue\] \[commit date\].'day of week' = 5

Keyword

Use keywords in the API query in the same manner as in the UI.

For example, when a ThoughtSpot UI query uses keywords growth of and sort by, the equivalent API query is as follows:

growth of \[revenue\] by \[commit date\]

Calendar

You can specify a custom calendar in the query. Use the calendar.calendar\_name format explicitly.

When the calendar name contains multiple words, enclose these words in single quotes.

For example, when a ThoughtSpot UI query is revenue by commit date fiscal, where the name of the calendar is fiscal, the equivalent API query is as follows:

\[revenue\] by \[commit date\] calendar.fiscal

For example, when a ThoughtSpot UI query is revenue by commit date my calendar, where the name of the calendar is my calendar, the equivalent API query is:

\[revenue\] by \[commit date\] calendar.'my calendar'

#### Functional limitations

-   To avoid join path ambiguities, a query can use only a single data source.
    
-   Search execution of query strings is not case-sensitive.
    
-   All column names in the data source must have unique names and must pass the _case-insensitivity_ test.
    
    For example, Columns `[Revenue]` and `[revenue]` are not unique.
    
-   Column names cannot contain square brackets, `[` or `]`.
    
-   Values must be enclosed in quotes, `‘’`, but they cannot contain quotes.
    
-   The API does not support **in-query formula** definitions. To use a formula, create it on the Model or a table using the ThoughtSpot UI, and then use the named formula inside the API query.
    
-   Users must be authenticated and have read access to the data source.
    
-   Your browser locale must be `en-US`. Swagger does not accept other variations of English, such as British English, or other languages. Your search keywords must also be in American English. Your column names and other data values do not need to be in American English. You can change your preferred locale to `en-US` in your browser settings.
    

## How to search data using the API

To query data using the API, follow these steps:

### Determine the GUID of the data source

To find the GUID of the Model, View, or Table, use one of the following methods:

Get data object GUID via API

Send an API request to the `/api/rest/2.0/metadata/search` v2 API endpoint with the following parameters in the metadata array:  

**Example**

```JSON
 "metadata": [
    {
      "identifier": "my_data_model",
      "type": "LOGICAL_TABLE"
    }
  ]
```

If you don’t know the exact name of the data source object, specify the metadata `type` as `LOGICAL_TABLE` in your API request, and then copy the GUID of the data object from the API response.

Find the GUID of the data object via UI

1.  Log in to your ThoughtSpot application instance:
    
2.  Navigate to **Data**.
    
    https://<your-thoughtspot-instance>/#/data/tables/
    
3.  On the **Data** > **Home** page, select the data object. For example, if the data source object is a Model, click **Models** and then open the Model.
    
4.  In the address bar of the web browser, note the GUID of the data source object. For example, in the following address string, the GUID is `9d93a6b8-ca3a-4146-a1a1-e908b71b963f`:
    
    https://<your-thoughtspot-instance>/#/data/tables/9d93a6b8-ca3a-4146-a1a1-e908b71b963f
    
5.  Copy the GUID.
    

### Run the search query

1.  In another browser, navigate to the following address:
    
    https://<your-thoughtspot-instance>/external/swagger/#!/tspublic%2Fv1/searchData
    
2.  Click `**POST** /tspublic/v1/searchdata`
    
3.  Specify values for request parameters. For more information, see [Search data API reference]({{navprefix}}/{{search-data-api}}#search-data-api-ref).
    
4.  Click **Try it out**, and note the results.
    
    You can also verify if the query returns the same data when you run it in the ThoughtSpot UI search bar (with slightly different syntax).
    

## Search data API reference

> **NOTE:** The search data API allows you to pass the search query string as query parameters in the URL. Therefore, ThoughtSpot recommends that you append the query string to the URL, instead of using the -d option to pass these parameters in your cURL requests.

#### Browser locale

The search data API supports the search query string or keywords in `en-US` language only. Due to this, your browser locale must be set to `en-US` for a successful API call. ThoughtSpot recommends that you set the locale to `en-US` in the HTTP header request.

Accept-Language: en-US

When making REST API call from a browser, you can set the Accept-Language header directly to override the browser locale for that request.

```javascript
// Using XMLHttpRequest
var xhr = XMLHttpRequest();
xhr.setRequestHeader('Accept-Language', 'en-US');
// ...

// Using Fetch
const fetchOptions = {
    method: 'POST',
    headers: {
      'Accept-Language': 'en-US',
      'Content-Type': 'application/json',
      // ...
    },
    // ...
}
```

### Resource URL

POST /tspublic/v1/searchdata

### Request parameters

 
| Query parameter | Description |
| --- | --- |
| 
`query_string`

 | 

_String_. The data search query string. For more information, see [Components of a search query]({{navprefix}}/{{search-data-api}}#components).

 |
| 

`data_source_guid`

 | 

_String_. The GUID of the data source, either a Model, a View, or a table.

 |
| 

`batchsize`

 | 

_Integer_. The batch size for loading search objects.

The system default is -1.

 |
| 

`pagenumber`

 | 

_Integer_. An alternate way to set offset for the starting point of the search results returned from the query. The default value is `-1`.

`indexingOffset = (pageNumber - 1) * batchSize`

 |
| 

`offset`

 | 

_Integer_. Attribute to offset the starting point of the search results returned from the query. Specify a 1-based offset. The default value is `-1`.

 |
| 

`formattype`

 | 

_String_. The format of the data.

Valid values are `COMPACT` or `FULL` JSON. The default value is `COMPACT`.

 |

### Example requests

cURL (COMPACT)

```cURL
curl -X POST \
--header 'Accept-Language: en-US' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
'https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/searchdata?query_string=%5Bsales%5D%20%5Bstore%20region%5D&data_source_guid=06517bd1-84c0-4bc6-bd09-f57af52e8316&batchsize=-1&pagenumber=-1&offset=-1&formattype=COMPACT'
```

cURL (FULL)

```cURL
curl -X POST \
--header 'Accept-Language: en-US' \
--header 'Content-Type: application/json' \
--header 'Accept: application/json' \
--header 'X-Requested-By: ThoughtSpot' \
'https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/searchdata?query_string=%5Bsales%5D%20%5Bstore%20region%5D&data_source_guid=06517bd1-84c0-4bc6-bd09-f57af52e8316&batchsize=-1&pagenumber=-1&offset=-1&formattype=FULL’
```

Request URL (COMPACT)

```html
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/searchdata?query_string=%5Bsales%5D%20%5Bstore%20region%5D&data_source_guid=06517bd1-84c0-4bc6-bd09-f57af52e8316&batchsize=-1&pagenumber=-1&offset=-1&formattype=COMPACT
```

Request URL (FULL)

```html
https://{ThoughtSpot-Host}/callosum/v1/tspublic/v1/searchdata?query_string=%5Bsales%5D%20%5Bstore%20region%5D&data_source_guid=06517bd1-84c0-4bc6-bd09-f57af52e8316&batchsize=-1&pagenumber=-1&offset=-1&formattype=FULL
```

### Example response

The data the API response contains four components:

1.  The `columnNames` array that contains a list of all column headers.
    
2.  The `data` array that contains a list of sub-arrays. Each sub-array represents a new row of data.
    
3.  A sampling ratio.
    
    The sampling ratio tells you the percentage of total data returned. A sampling ratio of `1` indicates that all the data in the Answer object was returned in the API response.
    

```JSON
{
  "columnNames": [
    "Store Region",
    "Total Sales"
  ],
  "data": [
    [
      "east",
      18934491.05134509
    ],
    [
      "midwest",
      29157090.327609923
    ],
    [
      "south",
      25484693.074720126
    ],
    [
      "southwest",
      34241076.52103955
    ],
    [
      "west",
      30848491.458509445
    ]
  ],
  "samplingRatio": 1,
  "totalRowCount": 5,
  "rowCount": 5,
  "pageSize": 100000,
  "offset": 0
}
```

### Response codes

 
| HTTP Code | Description |
| --- | --- |
| 
**200**

 | 

Successful retrieval of data from the data source

 |
| 

**400**

 | 

Invalid query/data source

 |