Run Data Explorer queries with the Discourse API

Virtually any action that can be performed through the Discourse user interface can also be triggered with the Discourse API. For a general overview of how to find the correct API request for an action, see Reverse engineer the Discourse API.

To run a Data Explorer query from the API, you need to make a POST request to /admin/plugins/explorer/queries/<query-id>/run. You can find the a Data Explorer query’s ID by visiting the query on your site and getting the value of the id parameter that you’ll see in your browser’s address bar.

This example query has an ID of 20 on my site. It returns a list of topics, ordered by views, for a given date:

--[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:

curl -X	POST "https://discourse.example.com/admin/plugins/explorer/queries/20/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <your_all_users_api_key>" \
-H "Api-Username: system" \
-F 'params={"viewed_at":"2019-06-10"}'

You will need to substitute your site’s base URL, query ID, and All Users API key into the example.

Handling queries that return over 1000 rows

:warning: The limit=ALL support for CSV exports has been removed, but should still work for JSON exports.

By default, the Data Explorer plugin returns a maximum of 1000 rows. You can bypass this limit by adding a limit=ALL parameter to the request. The query below will generate 10000 rows.

SELECT
*
FROM generate_series(1, 10000)

On my site, the query’s ID is 26. Here’s how to get all the query’s rows:

curl -X POST "https://discourse.example.com/admin/plugins/explorer/queries/26/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <your_all_users_api_key>" \
-H "Api-Username: system" \
-F "limit=ALL"

Paginating the results

For a query that returns a very large data set, you can paginate the results. Given this example query (it has an id of 27 on my site):

--[params]
-- integer :limit = 100
-- integer :page = 0

SELECT
*
FROM generate_series(1, 10000)
OFFSET :page * :limit
LIMIT :limit

you can return the first 100 rows with

curl -X POST "https://discourse.example.com/admin/plugins/explorer/queries/27/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <your_all_users_api_key>" \
-H "Api-Username: system" \
-F 'params={"page":"0"}'

You can keep incrementing the value of the page parameter until the result_count that is returned is 0.

Removing relations data from the results

When Data Explorer 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, pass a download=true parameter with your request:

curl -X POST "https://discourse.example.com/admin/plugins/explorer/queries/27/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <your_all_users_api_key>" \
-H "Api-Username: system" \
-F 'params={"page":"0"}' \
-F "download=true"

Api authentication

For details about authenticating API requests, see the Authentication section of our API Documentation: https://docs.discourse.org/

Common Questions

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/explorer/queries.json to get a list of all queries on the site.

Is it possible to send parameters with the post request?

Yes, the first code example in the OP should give you what you are looking for. It passes a viewed_at parameter, but a similar approach would work with a category_id parameter.

Additional Resources


Last Reviewed by @AlexDev on 2022-07-20T00:00:00Z

30 Likes
Discourse Data Explorer
What cool data explorer queries have you come up with?
Reverse engineer the Discourse API
"DataExplorer::ValidationError: Missing parameter end_date of type string
How can I get the list of Discourse Topic IDs dynamically
Get Latest topic for Current user
Best API for All First Posts in a Category
Watching API
Reports by Discourse
Download result of queries into Google Spreadsheet
Who's online "API"?
Is there any endpoint that would provide a user's external account IDs from their Discourse ID?
API post request without an Accept header returns 406
Validation error even when parameter passed while running data explorer API with Curl
How to get a full list of badges of all users
API rate limits
Category API request downloads all topics
Getting recently updated posts using the REST API
`DataExplorer::ValidationError: Missing parameter` when running Data Explorer queries with [params] via API
`DataExplorer::ValidationError: Missing parameter` when running Data Explorer queries with [params] via API
Backend data retrieve for analytics
Discourse-user-notes API
Admin Dashboard Report Reference Guide
Get total list of topics and their view counts from Discourse API
Can a moderator have access to the stats panel on the admin dashboard?
How to get all the deleted posts for a specific topic
Discourse forum traffic query data
Discourse Data Explorer
Download a user's posting history via Discourse API?
Discourse Data Explorer Query Response to Slack
Filter topics in category containing file attachments
Discord Integration with Webhooks
TimeStamp of Tag