# Run Data Explorer queries with the Discourse API **URL:** https://meta.discourse.org/t/run-data-explorer-queries-with-the-discourse-api/120063 **Category:** Integrations **Tags:** data-explorer, rest-api, how-to **Created:** [June 11, 2019, 7:43pm UTC](https://meta.discourse.org/t/run-data-explorer-queries-with-the-discourse-api/120063 "2019-06-11T19:43:38Z") **Posts on this page:** 1 **Showing post:** 1
### Author: ![Discourse](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/discourse/32/148734_2.png) [@Discourse](https://meta.discourse.org/u/Discourse) #### Post date: [June 11, 2019, 7:43pm UTC](https://meta.discourse.org/t/run-data-explorer-queries-with-the-discourse-api/120063/1 "2019-06-11T19:43:38Z")
> 🔖 This guide explains how to use the Discourse API to create, run, and manage queries with the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin. > > 🙋 Required user level: Administrator Virtually any action that can be performed through the Discourse user interface can also be triggered with the Discourse API. This document provides a comprehensive overview for utilizing the API specifically in conjunction with the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin. For a general overview of how to find the correct API request for an action, see: [Reverse engineer the Discourse API](https://meta.discourse.org/t/how-to-reverse-engineer-the-discourse-api/20576) . ## Running a [Data Explorer](https://meta.discourse.org/t/32566?silent=true) query To run a [Data Explorer](https://meta.discourse.org/t/32566?silent=true) query via the API, make a `POST` request to `/admin/plugins/discourse-data-explorer/queries//run`. You can find the query ID by visiting it through your Discourse site and checking the `id` parameter in the address bar. Below is an example query with an ID of `20` that returns topics by views on a specified date: ```sql --[params] -- date :viewed_at SELECT topic_id, COUNT(1) AS views_for_date FROM topic_views WHERE viewed_at = :viewed_at GROUP BY topic_id ORDER BY views_for_date DESC ``` This query can be run from a terminal with: ```bash curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/20/run" \ -H "Content-Type: multipart/form-data;" \ -H "Api-Key: " \ -H "Api-Username: system" \ -F 'params={"viewed_at":"2019-06-10"}' ``` Note that you’ll need to replace the ``, `` with your API key and domain. ## Handling large datasets The [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin limits JSON results to 1000 rows by default (controlled by the hidden `data_explorer_query_result_limit` site setting). You can override this per-request by passing a `limit` parameter, up to a maximum of 10,000: ```bash curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/20/run" \ -H "Content-Type: multipart/form-data;" \ -H "Api-Key: " \ -H "Api-Username: system" \ -F "limit=5000" ``` For datasets larger than 10,000 rows, you’ll need to paginate at the SQL level. You can use the example query below: ```sql --[params] -- integer :limit = 100 -- integer :page = 0 SELECT * FROM generate_series(1, 10000) OFFSET :page * :limit LIMIT :limit ``` To fetch the results page-by-page, increment the `page` parameter in the request: ```bash curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/27/run" \ -H "Content-Type: multipart/form-data;" \ -H "Api-Key: " \ -H "Api-Username: system" \ -F 'params={"page":"0"}' ``` Stop when `result_count` is zero. For additional information about handling large datasets, see: [Result Limits and Exporting Queries](https://meta.discourse.org/t/discourse-data-explorer/32566#p-138280-result-limits-and-exporting-queries-7) ## Removing `relations` data from the results When [Data Explorer](https://meta.discourse.org/t/32566?silent=true) queries are run through the user interface, a `relations` object is added to the results. This data is used for rendering the user in UI results, but you are unlikely to need it when running queries via the API. To remove that data from the results, add a `download=true` parameter with your request: ```bash curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/27/run" \ -H "Content-Type: multipart/form-data;" \ -H "Api-Key: " \ -H "Api-Username: system" \ -F 'params={"page":"0"}' \ -F "download=true" ``` ## API authentication Details about generating an API key for the requests can be found here: [Create and configure an API key](https://meta.discourse.org/t/create-and-configure-an-api-key/230124) . If the API key is only going to be used to run [Data Explorer](https://meta.discourse.org/t/32566?silent=true) queries, you can select “Granular” from the Scope drop down menu, then select the “run queries” scope. ![Screenshot 2024-04-12 at 3.11.52 PM](https://global.discourse-cdn.com/meta/original/4X/d/e/0/de08dc117fcce3b54d59f22def342e04f9cbe301.png) ## FAQs > _Is there any api endpoint I can use to get the list of reports and the ID numbers? I want to build a dropdown with the list in it?_ Yes, you can make an authenticated GET request to `/admin/plugins/discourse-data-explorer/queries.json` to get a list of all queries on the site. > _Is it possible to create queries through the api?_ Yes. Documentation on how to do that are at [Create a Data Explorer query using the API](https://meta.discourse.org/t/create-a-data-explorer-query-using-the-api/394388) > _Is it possible to send parameters with the post request?_ Yes, include SQL parameters using the `-F` option, as shown in the examples. > _Is CSV export for queries supported by the API?_ Yes. Append `.csv` to the run endpoint URL to get results in CSV format: ```bash curl -X POST "https://your-site-url/admin/plugins/discourse-data-explorer/queries/20/run.csv" \ -H "Api-Key: " \ -H "Api-Username: system" \ -F 'params={"viewed_at":"2019-06-10"}' ``` CSV responses default to returning up to 10,000 rows. You can pass a `limit` parameter to reduce this. ## Additional resources - [Create a Data Explorer query using the API](https://meta.discourse.org/t/create-a-data-explorer-query-using-the-api/394388) - [Create and configure an API key](https://meta.discourse.org/t/create-and-configure-an-api-key/230124) - [Reverse engineer the Discourse API](https://meta.discourse.org/t/how-to-reverse-engineer-the-discourse-api/20576) - [GitHub - lee-dohm/execute-discourse-query: Executes a Discourse data-explorer query via the API · GitHub](https://github.com/lee-dohm/execute-discourse-query) --- _[View the full topic](https://meta.discourse.org/t/run-data-explorer-queries-with-the-discourse-api/120063)._