How to Query the Datastore

How to use the different DataStore endpoints

There are three ways that the Datastore API 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.

1. API Requests: Schema Information

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

Resource ID of datastore active resource.

Example URL

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

2. API Requests: Native

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 for more details)

Parameter	Descriptions
resource_id	[Required] The resource unique identifier
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 (exact match)
fields	Limit which columns are returned
sort	Sort results

3. API Requests: SQL

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