# How to query the Tabular Data Endpoints

There are three ways that the Tabular Data endpoints can be queried, each accessed via it's own endpoint. See below:

1. **Schema Information (`datastore_info`):** This endpoint returns information about the data within the resource like column names and data types. This is helpful to better understand the data instead of calling the entire data table.
   1. Use `datastore_info` for simple information on the data resources.
2. **Native (`datastore_search`):** This endpoint provides a simple, URL-based interface for querying records within a data resource. If you are starting out with APIs, this is the one you should use. It supports standard functionality including filtering by field values, text search, pagination, field selection, and sorting.
   1. Use `datastore_search` when working with simple filters, browsing data, or building lightweight applications or a Power BI dashboard.
3. **SQL (`datastore_search_sql`):** This endpoint allows for more advanced querying using full SQL syntax. It is useful when complex conditions like aggregations, or joins within multiple resources are needed. It offers greater flexibility but also requires a proper understanding of SQL.
   1. Use `datastore_search_sql` for more analytical purposes or when multiple conditions and calculations are required.

For more detailed information, please see the [CKAN DataStore API documentation](https://docs.ckan.org/en/2.9/maintaining/datastore.html#the-datastore-api).

#### 1. Query requests: Schema information <a href="#id-1.-api-requests-schema-information" id="id-1.-api-requests-schema-information"></a>

This is a basic information endpoint which simply returns information about the data resource. This can be used to learn more about the column names and data types by retrieving a dictionary describing the columns and their types.

**Endpoint**

`https://data.humdata.org/api/3/action/datastore_info`

**Parameters**

ID of the resource.

**Example URL**`https://data.humdata.org/api/3/action/datastore_info?id=<resource_id>`

#### 2. Query requests: Native <a href="#id-2.-api-requests-native" id="id-2.-api-requests-native"></a>

With the **endpoint**, **token**, and **resource\_id**, you can now query data using standard API calls by configuring the URL like follows:

**Endpoint**

`https://data.humdata.org/api/3/action/datastore_search`

**Example:** Retrieve the rows in a dataset where the country is “Kenya” for 2024.

`https://data.humdata.org/api/3/action/datastore_search?id=<resource_id>&filters={"country":"Kenya",”year”:2024}`

**Common parameters (see** [**the CKAN DataStore documentation**](https://docs.ckan.org/en/latest/maintaining/datastore.html#ckanext.datastore.logic.action.datastore_search) **for more details)**

| `resource_id` | <mark style="color:$danger;">\[Required] The resource unique identifier</mark>                              |
| ------------- | ----------------------------------------------------------------------------------------------------------- |
| `limit`       | Maximum number of results to return                                                                         |
| `offset`      | Start index for pagination                                                                                  |
| `q`           | Search across all data (can pass as dictionary to limit to a specific column)                               |
| `filters`     | ​[Filter specific fields](https://docs.ckan.org/en/latest/maintaining/datastore.html#filters) (exact match) |
| `fields`      | Limit which columns are returned                                                                            |
| `sort`        | Sort results                                                                                                |

#### 3. Query Requests: SQL <a href="#id-3.-api-requests-sql" id="id-3.-api-requests-sql"></a>

For more advanced API queries, the SQL API supports a full subset of PostgreSQL syntax. It's ideal for advanced queries, aggregations, joins (within limits), and complex filters. It also requires the **endpoint**, **token**, and **resource\_id**.

**Endpoint**

`GET /datastore_search_sql`

**Parameters:** `sql` (Required) A SQL query string. Must reference the correct resource (table name is the `resource_id`).

**Example: WHERE clause**

`https://data.humdata.org/api/3/action/datastore_search_sql?sql=SELECT+*+FROM+"id"+WHERE+country='Kenya'+AND+year=2024`

**Example SQL Query**

`SELECT country, AVG(value) FROM "id" WHERE indicator = 'population' AND year = 2024 GROUP BY country`

Note: Be sure to URL-encode your SQL queries when sending them via GET (e.g. urlib.parse in Python).


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.humdata.org/build/hdx-apis/tabular-data-endpoints/how-to-query-the-tabular-data-endpoints.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.