使用 Discourse API 运行数据资源管理器查询

[Discourse]

This guide explains how to use the Discourse API to create, run, and manage queries with the Data Explorer 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 plugin.

For a general overview of how to find the correct API request for an action, see: Reverse engineer the Discourse API.

Running a Data Explorer query

To run a Data Explorer query via the API, make a POST request to /admin/plugins/explorer/queries/<query-id>/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:

--[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://your-site-url/admin/plugins/explorer/queries/20/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <api-key>" \
-H "Api-Username: system" \
-F 'params={"viewed_at":"2019-06-10"}'

Note that you’ll need to replace the <api-key>, <your-site-url> with your API key and domain.

Creating a query via the API

To create a Data Explorer query via the API, you’ll need to make a POST request to /admin/plugins/explorer/queries.

You will also need to specify the query name and sql to use for the new query in the API call.

Below is an example of how to create a new query using the API:

curl -X POST "https://your-site-url/admin/plugins/explorer/queries" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <api-key>" \
-H "Api-Username: <username>" \
-F 'query[name]=Example Query' \
-F 'query[sql]=SELECT COUNT(*) FROM users'

This API call will return a response like:

{"query":{"id":49,"name":"Example Query","description":null,"username":"<username>","group_ids":[],"last_run_at":null,"user_id":1,"sql":"SELECT COUNT(*) FROM users","param_info":[],"created_at":"2025-03-13T18:41:44.226Z","hidden":false}}%

You can then run the query by using the query ID from the response (in this case, 49 ).

Returned results will be structured within the rows field.

Handling large datasets

The Data Explorer plugin limits results to 1000 rows by default. To paginate through larger datasets, you can use the example query below:

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

curl -X POST "https://your-site-url/admin/plugins/explorer/queries/27/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <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

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, add a download=true parameter with your request:

curl -X POST "https://your-site-url/admin/plugins/explorer/queries/27/run" \
-H "Content-Type: multipart/form-data;" \
-H "Api-Key: <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.

If the API key is only going to be used to run Data Explorer queries, you can select “Granular” from the Scope drop down menu, then select the “run queries” scope.

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

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?

While JSON output is standard, you can manually convert results to CSV. Native CSV export is no longer supported.

Additional resources

• Create and configure an API key
• GitHub - lee-dohm/execute-discourse-query: Executes a Discourse data-explorer query via the API
• Reverse engineer the Discourse API

[37Rb (Ryan)]

@simon

limit=ALL 对 CSV 导出的支持

这条评论似乎暗示可以从 API 进行 CSV 导出。有可能吗？只是好奇，因为我需要 CSV 格式的数据。我可以随时获取 JSON 格式并转换为 CSV，但如果有一种内置的 CSV 获取方式会更简单一些。

[francis_exalate (Francis Martens (Exalate))]

是否可以进行“在过去50秒内创建或更新”的查询？

[Canapin (Coin-coin le Canapin)]

AI说

-- [params]
-- int :seconds = 50

SELECT
    p.id AS post_id,
    p.created_at,
    p.updated_at,
    p.raw AS post_content,
    p.user_id,
    t.title AS topic_title,
    t.id AS topic_id
FROM posts p
INNER JOIN topics t ON t.id = p.topic_id
WHERE
    (EXTRACT(EPOCH FROM (NOW() - p.created_at)) <= :seconds
    OR EXTRACT(EPOCH FROM (NOW() - p.updated_at)) <= :seconds)
    AND p.deleted_at IS NULL
    AND t.deleted_at IS NULL
ORDER BY p.created_at DESC
LIMIT 50

快速测试了一下，似乎有效

[Moin]

我没有看到您使用查询添加的“喜欢 ”。
我认为您需要使用 post_actions

--[params]
--string :timespan = 50 seconds

SELECT post_id,
       user_id,
       created_at,
       updated_at,
       deleted_at
FROM  post_actions
WHERE post_action_type_id=2 AND updated_at > NOW() - INTERVAL :timespan

结果更美观的版本

--[params]
--string :timespan = 50 seconds
--boolean :include_in_timespan_deleted = false

SELECT
  post_id,
  user_id,
  CASE
    WHEN EXTRACT(EPOCH FROM (NOW() - created_at)) < 60 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - created_at))), ' seconds ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - created_at)) < 3600 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - created_at)) / 60), ' minutes ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - created_at)) < 86400 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - created_at)) / 3600), ' hours ago')
    ELSE CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - created_at)) / 86400), ' days ago')
  END AS relative_created_at,
  CASE
    WHEN EXTRACT(EPOCH FROM (NOW() - updated_at)) < 60 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - updated_at))), ' seconds ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - updated_at)) < 3600 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - updated_at)) / 60), ' minutes ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - updated_at)) < 86400 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - updated_at)) / 3600), ' hours ago')
    ELSE CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - updated_at)) / 86400), ' days ago')
  END AS relative_updated_at,
  CASE
    WHEN deleted_at IS NULL THEN 'no'
    WHEN EXTRACT(EPOCH FROM (NOW() - deleted_at)) < 60 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - deleted_at))), ' seconds ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - deleted_at)) < 3600 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - deleted_at)) / 60), ' minutes ago')
    WHEN EXTRACT(EPOCH FROM (NOW() - deleted_at)) < 86400 THEN CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - deleted_at)) / 3600), ' hours ago')
    ELSE CONCAT(ROUND(EXTRACT(EPOCH FROM (NOW() - deleted_at)) / 86400), ' days ago')
  END AS relative_deleted_at
FROM
  post_actions
WHERE
  post_action_type_id = 2
  AND updated_at > NOW() - INTERVAL :timespan
  AND (
    :include_in_timespan_deleted = false
    OR (deleted_at IS NOT NULL AND deleted_at > NOW() - INTERVAL :timespan)
  )

[Canapin (Coin-coin le Canapin)]

哦，我读错了！我把这句话读成了“是否可以进行类似‘创建或更新在过去 50 秒内’的查询？”
（注意单引号的位置）

[francis_exalate (Francis Martens (Exalate))]

谢谢大家，

我在这里发帖是因为 ask.discourse 指引我来的。
我想知道 API 是否提供了此功能。

[Canapin (Coin-coin le Canapin)]

是的，当然。

您在数据资源管理器中创建查询，然后按照本指南中的说明通过 API 运行查询。

我刚刚通过 API 运行了 Moin 的查询，它正确返回了预期的结果。

[tanya_byrne (kukulaka)]

我也想知道这个问题。通过 API 导出数据是否只有 JSON 这一种方式，还是数据浏览器也支持 CSV 导出？

[francis_exalate (Francis Martens (Exalate))]

谢谢大家，

抱歉回复晚了——我暂时离线了一会儿。

我目前正在搜索今天创建的所有主题/帖子，并过滤掉在时间戳之前更新的主题/帖子。

[Canapin (Coin-coin le Canapin)]

@francis_exalate

我目前正在搜索今天创建的所有主题/帖子，并过滤掉在时间戳之前更新的主题/帖子。

如果你不想亲自动手，可以向 ask.discourse.com 上的机器人提问。它通常在与 Discourse 相关的 SQL 查询方面相当准确（但不要假设它是正确的，请务必检查代码以确保）。

[srinivas.chilukuri]

@Discourse

-H "Content-Type: multipart/form-data; `

---

Content-Type 标头是否正确？
在开发者工具中检查带有参数的数据浏览器查询时，Content-Type 标头显示为：

Content-Type: application/x-www-form-urlencoded; charset=UTF-8

但是，当前的 cURL 命令包含：

-H "Content-Type: multipart/form-data;"

---

[blake (Blake Erickson)]

• multipart/form-data
• application/x-www-form-urlencoded
• application/json

在发起 API 请求时，以上都是有效的内容类型（content-types）。

[srinivas.chilukuri]

@blake
language python
library requests
您能否提供一个包含三个参数的 API 示例 Data Explorer 查询
refer [Topic]( Passing params to Data Explorer using API requires enclosing a value ) 其中说明参数需要严格用双引号括起来

[blake (Blake Erickson)]

当然，这是一个使用 Python 的示例：

import json
import requests

API_KEY      = "YOUR_API_KEY"
API_USERNAME = "system"
QUERY_ID     = 20
SITE_URL     = "https://your-site-url"

# 所有值都必须是字符串
params = {
    "user_id":   "2",
    "viewed_at": "2019-06-10",
    "limit":     "5"
}

# Data Explorer 期望将参数作为 JSON 编码的字符串
payload = {
    "params": json.dumps(params)
}

url = f"{SITE_URL}/admin/plugins/explorer/queries/{QUERY_ID}/run"
headers = {
    "Api-Key":       API_KEY,
    "Api-Username":  API_USERNAME,
    "Content-Type":  "application/json"
}

r = requests.post(url, headers=headers, json=payload)
r.raise_for_status()
print(r.json())