Discourse Data Explorer

[Discourse]

	Summary	Discourse Data Explorer allows you to make SQL queries against your live database, allowing for up-to-the-minute stats reporting.
	Install Guide	This plugin is bundled with Discourse core. There is no need to install the plugin separately.

As this is an official plugin maintained by the Discourse team, Support, Bug, UX, and Feature requests can be made in the respective categories here on Meta, and tagged with the appropriate plugin tag. Click on a link below to get one started.

Support Bug UX Feature

If you’re looking for examples or support for any custom queries, you can find lots of topics in our Data & reporting category under the sql-query tag. If there’s not one to suit your particular needs, please feel free to create a new topic to ask the community for some help. Query Help

Hosted by us? This plugin is available on our Business and Enterprise plans. Data Explorer | Discourse - Civilized Discussion

After installing the plugin, head to /admin/plugins .

Click on the “Settings” button, and turn on data explorer enabled, then return and reload the page.
There should now be a tab in the stacked nav called “ Data Explorer ”. Select it or head to /admin/plugins/explorer , then use the + , Import or Run buttons to get started.

Default queries

On a fresh install, the data explorer now ships with several queries that can help you draw insights from your forum’s activity. Open any query and click Run to try it out.

Here's what the stock queries look like.

Note: Default queries are picked up from this file: discourse-data-explorer/lib/discourse_data_explorer/queries.rb at main · discourse/discourse-data-explorer · GitHub

If you have an awesome query that you want everyone to have, make a PR just like this one.

Default queries cannot be edited, feel free to copy the sql and paste it into a new query if you’d like to modify them.

Writing queries

When you click any of the Edit buttons on a query view, or start a new Explorer Query, you are presented with a screen like the one below:

The top two boxes are the name and description of the query. The name is what you’re selecting in the dropdown box, and the description should be used for things like explaining what to put in the query parameters or to describe what data is being queried.

The left pane is where you write the SQL query. Minor syntax highlighting and checking is provided.

The schema explorer is on the right side of the writing pane. Note the red column names with a warning symbol, such as email , password_hash , salt - these are “Sensitive” columns. Try to avoid querying them if possible, reconsider querying them if you are in a public location, and definitely do not post screenshots of queries checking those columns without thinking about what you’re doing first.

Automatically running queries

While you can always run a query by clicking the Run button, you can save time when loading a query by adding the run parameter to the URL.

For example: /admin/plugins/explorer/queries/123?run

When this parameter is added to the URL, the query will run as soon as you load the page, so you don’t need to click the Run button to see the results. This is especially helpful for queries you run frequently, allowing you to bookmark them or add them to your sidebar with the run parameter included for a one-click report.

Importing queries

For an example, let’s import one that I wrote. Download this file, then open the import dialog and select the file to be uploaded or paste the query in the text box. Click Import to save it and then click Run to see the results.

most-common-likers.dcquery (1).json (442 Bytes)

Looks like all of the Discourse developers like each other quite a bit

Searching for queries

If you’ve accumulated too many awesome queries, no problem! The search bar can help you filter through titles and descriptions.

Allow non-staff groups to run queries

You can add specific groups (including moderators) to a query to grant them access to it:

After saving, you can find it from the Groups page for that particular group on the Reports tab:

Click any query to open it and then the Run button to see the results. You can also bookmark group reports by clicking the Bookmark button.

Sharing queries with predefined parameters

If you have included user-defined parameters in your query, you can share a link to the query and include the parameters in the URL so they will be pre-filled when the page loads.

You must include parameters in the URL in this format params={"parameter_name":"value"}. Separate multiple parameters with commas. For example:

discourse.example.com/g/my-group/reports/6?params={"param1":"value1","param2":"value2"}

Couple with the run parameter mentioned above, you can share a report with predefined parameters that runs automatically when loaded.

Result limits and exporting queries

With large queries, you may notice that there is a limit on the number of rows that the Data Explorer will display. By default, the limit is set to 1000 rows. This is to prevent excessively large queries from slowing down or disrupting the performance of your Discourse instance.

If you want to bypass this limit, you have a few of options:

1. Pagination: You can paginate your results. Instead of trying to return all results at once, you can write your query to return a specific range of results. You can then adjust this range to view different sections of your data. This won’t increase the limit, but it will allow you to view all your data in sections.
2. Increase the limit: If you have access to the Discourse server and are comfortable making changes to Discourse settings, you can increase the limit. However, this should be done with caution. Increasing the limit can put a lot of load on your server if you’re not careful, especially if multiple users are running large queries at the same time.
3. Download the Results: You also have the option to download the query results instead of viewing them directly in Discourse by clicking either the JSON or CSV button after running the query. Data downloaded from a query in JSON or CSV format will have a much higher results limit of 10,000 rows. For programmatic purposes, you want want to opt for the Json format, or if you’d like to work on the data in a spreadsheet application, the CSV format might be more convenient.

It’s generally a good idea to try and write more efficient queries, or to filter your data so that you’re only retrieving what you need, rather than trying to bypass the limit.

You can also export the SQL query itself in .JSON format by clicking the Export button. Queries that are exported using this method can then be imported into other Discourse sites, or used with third party applications.

Query plan

The “Include query plan” checkbox can be used for better understanding and optimizing your SQL queries.

When you check this box and run a query, Discourse will display the sequence of operations used to access data in the Discourse database. This can include operations like scans, joins, sorts, and other database actions. By examining the query plan, you can see exactly how the database is executing your query.

The Data Explorer tutorial series

We also have a dedicated sql-tutorial series about the Data Explorer! If you’re interested in learning more about writing SQL queries in Discourse, we highly recommend reading through the topics in this series:

• Data Explorer Tutorial - Part 1 - Writing Your First Query
• Data Explorer Tutorial - Part 2 - Discourse SQL Basics
• Utilizing Parameters in Data Explorer Queries
• Formatting Data Explorer Table Results

Last edited by @hugh 2025-09-03T00:45:12Z

Last checked by @hugh 2025-09-03T00:45:21Z

Check document

Perform check on document:

[HamMan2118]

你好！

我在这个帖子中询问了如何复制搜索查询中所有主题的链接。我被引导到这里。所以，我问了我们的论坛的管理员，我们是否安装了这个插件，我们确实安装了。但是，我们不知道如何使用它来使用这个插件将搜索查询中的所有链接复制到CSV文件中。有人以前做过这件事吗？也许可以指引我们一下？

请注意，我是论坛的普通用户，我没有管理权限。但是，我正在将此信息转达给有权限的网站管理员，他可以为我做这件事。

提前感谢！

[Arkshine]

你好 Preston！

Data Explorer 插件允许你创建 SQL 查询。

你想创建一个新的查询来列出你正在寻找的主题。

然后，当你运行查询时，你会看到一个 CSV 按钮，用于将结果导出到 CSV 文件。

希望这有帮助！

如果你在创建查询方面需要更多帮助，请告诉我们！

[HamMan2118]

谢谢！我会转达的 :拥抱:

[gormus (Osman Görmüş)]

您好，我有一个关于这个很棒插件的功能请求。

我使用数据探索器创建的一些报告，如果我能在仪表板报告（/admin/reports）页面列出它们，对我的团队将非常有帮助。

是否可以为每个数据探索器查询添加一个选项，使其能在报告页面列出？谢谢。

例如：

• 在报告页面列出此自定义查询

[kOS (KilianS)]

感谢这个功能！

我正在探索如何编写自定义指标。我们正在将 Discourse 与 Workadvent.re（一个基于 2D 头像的 A/V 协作工具）一起使用。
我正在那里运行一个自定义脚本，通过 webhook 将活动记录到 Google 表格中。为了将所有内容集中在我们核心工具（Discourse）中，将这些数据打印到 Discourse 中将非常棒……
有什么建议吗？！

[davidgs (David G. Simmons)]

我真的很希望有一个查询生成器可以帮助编写 SQL。我的 SQL 很老旧而且生疏，手动编写 SQL 容易出错且耗时。如果有一种更简单的方法就好了！

[Tris20 (Tristan)]

您好 @davidgs，欢迎来到 Meta

@davidgs

I was really hoping that there was a query builder that would help with writing SQL.

您尝试过使用像 ChatGPT 这样的 LLM 吗？它对 Discourse 有相当的理解，并且在我需要制作数据探索器查询时也相当可靠。

我经常这样写提示：“编写一个 Discourse 数据探索器查询，从 a,b,c 中返回 x,y,z，其中…” 然后就能得到相当不错的结果。

在本页的右上角，您可能还会看到一个小机器人图标，如下所示。

如果那里有的话，您也可以尝试集成在 Meta 中的 AI Helper Bot。

[Jagster (Jakke Lehtonen)]

@Tris20

在此页面右上角，您可能还会看到一个小机器人图标，如下所示

我们大多数人都看不到它。但总有 ask.discourse.com

[Tris20 (Tristan)]

@Jagster

我们大多数人都没注意到。但总有 ask.discourse.com

是的，我不记得这是否仍然只是一个3级信任才能使用的功能。你推荐的ask.discourse.com网站是个好主意。

作为参考示例，我将“写一个 discourse 数据探索查询，返回所有说过“software”一词的用户列表”输入到ask.discourse.com的文本输入栏中，得到了以下查询：

SELECT 
    p.user_id, 
    u.username,
    COUNT(p.id) AS post_count
FROM 
    posts p
JOIN 
    users u ON u.id = p.user_id
WHERE 
    p.raw ILIKE '%software%'
GROUP BY 
    p.user_id, u.username
ORDER BY 
    post_count DESC;

这相当合理。我还可以证明它能处理比这复杂得多的查询。

[GuidoD (Guido Drehsen)]

我安装 Discourse 和 Data Explorer 的最新更新大约两个小时后，数据浏览器的导出数据不再有效。数据本身创建正常，但无法再导出。

点击 JSON 或 CSV 按钮只会闪烁一下，然后什么也没发生。大约两周前它还能正常工作。

在网络浏览器控制台中，我看到以下错误：
CSV：

Uncaught TypeError: this.args.query is undefined
    _downloadResult query-result.js:310
    downloadResultCsv query-result.js:287
    _triggerAction d-button.gjs:160
    Ember 12
    _triggerAction d-button.gjs:157
    click d-button.gjs:106

JSON：

Uncaught TypeError: this.args.query is undefined
    _downloadResult query-result.js:310
    downloadResultJson query-result.js:283
    _triggerAction d-button.gjs:160
    Ember 12
    _triggerAction d-button.gjs:157
    click d-button.gjs:106

[pento (Gary)]

感谢您的 bug 报告，@GuidoD！

我今天早些时候合并了一个针对此问题的修复程序，请尝试更新并确认它是否对您有效。

FIX: Query downloads were being passed an incorrect query object.

[GuidoD (Guido Drehsen)]

@pento

请尝试更新并确认它对您有效。

感谢您快速修复。
我可以确认它又可以正常工作了。

[GuidoD (Guido Drehsen)]

少量数据导出到 JSON 和 CSV 都可以正常工作。但对于大量数据，CSV 导出工作正常，但 JSON 导出在 nginx 中会导致 502 错误网关（可能是因为 30 秒后超时，而 JSON 文件创建时间超过 30 秒）。

有没有简单的方法可以增强 nginx 的超时值？

如果我缩短查询的时间范围，csv 文件为 12.9 MB，json 文件为 645 MB。总的 csv 文件为 13.5 MB，只比这个大一点点，但 json 文件由于 nginx 错误而未创建。

或者，JSON 文件创建是否由于某些奇怪的数据而出错？JSON 文件创建会生成日志文件吗？

[riking (Kane York)]

JSON 查询默认比 CSV 查询运行更高的外部 LIMIT。在查询内部添加自定义分页以确保其按时完成。

[GregorSondermeier]

是否可以从数据浏览器执行 UPDATE 语句？

我正在尝试对某个表进行批量更新，但出现此错误：

PG::FeatureNotSupported: ERROR:  WITH 查询“query”不包含 RETURNING 子句
第 29 行： ) SELECT * FROM query

[riking (Kane York)]

不，Data Explorer 仅执行只读事务。（如果您更新查询以包含 RETURNING 子句，您将收到只读事务错误。）

如果您是自托管用户，请使用“启动器输入”指南之一，例如

Edit a user preference for everyone or a subset of users

This guide explains how to edit a user preference for everyone or a subset of users in Discourse. Required user level: System Administrator Console access is required. If you need to update the user preference for all of your users or a large subset of users, you can do so via the rails console. Summary In this guide, you’ll learn: How to access the Rails console for making bulk changes Examples of modifying user preferences How to identify se…

如果您已经设计好 UPDATE，那么您可以 psql discourse 而不是 rails c。

如果您使用的是托管服务，请联系支持部门让他们执行更改。

[GregorSondermeier]

感谢您的回复。说得通。我们使用的是托管服务，所以我会联系他们的支持团队。

[Naman_Gala]

如果我与版主组共享 SQL 查询，该组中的用户如何从他们的帐户运行查询？他们看不到数据浏览器插件。