# Discourse Data Explorer
**URL:** https://meta.discourse.org/t/discourse-data-explorer/32566
**Category:** Plugin
**Tags:** official, data-explorer, included-in-core
**Created:** [August 26, 2015, 11:46pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566 "2015-08-26T23:46:06Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Discourse](https://meta.discourse.org/u/Discourse)
#### Post date: [August 26, 2015, 11:46pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/1 "2015-08-26T23:46:06Z")
| | | |
| --- | --- | --- |
| :discourse2: | **Summary** | **Discourse** [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 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. |
> [@](#):
>
> :discourse2: As this is an #official plugin maintained by the Discourse team, #Support, #Contribute > Bug, #Contribute > UX, and #Contribute > 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**](https://meta.discourse.org/new-topic?category_id=6&body=%3E%20Before%20asking,%20did%20you%20search%20first%3F%20Press%20%F0%9F%94%8D%20at%20the%20upper%20right%20to%20search.&tags=data-explorer "Ask for support on configuring and using Discourse Data Explorer") [đ **Bug**](https://meta.discourse.org/new-topic?category_id=1&tags=data-explorer "A bug report means something is broken, preventing normal/typical use of the plugin") [đ **UX**](https://meta.discourse.org/new-topic?category_id=9&tags=data-explorer "Discussion about the user interface of Discourse Data Explorer, and how features are presented (including language and UI elements)") [đĄ **Feature**](https://meta.discourse.org/new-topic?category_id=2&tags=data-explorer "Discussion about how existing Discourse Data Explorer features can be improved or enhanced, and how proposed new features could work")
> âš 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**](https://meta.discourse.org/new-topic?category_id=148&tags=sql-query "Create a new topic to ask for help or share your top examples")
> :discourse2: Hosted by us? This plugin is available on our Business and Enterprise plans. [Data Explorer | Discourse - Civilized Discussion](https://www.discourse.org/plugins/data-explorer.html)
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](https://meta.discourse.org/t/32566?silent=true) â. Select it or head to `/admin/plugins/discourse-data-explorer` , then use the + , Import or Run buttons to get started.
### Default queries
On a fresh install, the [data explorer](https://meta.discourse.org/t/32566?silent=true) 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](https://github.com/discourse/discourse-data-explorer/blob/main/lib/discourse_data_explorer/queries.rb)
>
> 
>
> If you have an awesome query that you want everyone to have, make a PR just [like this one.](https://github.com/discourse/discourse-data-explorer/commit/931bbb67a07a57965c3da8ca471ee2283eeb1f27)
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/discourse-data-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](https://global.discourse-cdn.com/meta/original/3X/f/b/fb837a5551217ea43ca1a732681f641bf952856c.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:
```plaintext
discourse.example.com/g/my-group/reports/6?params={"param1":"value1","param2":"value2"}
```
Couple with the `run` parameter [mentioned above](https://meta.discourse.org/t/discourse-data-explorer/32566#p-138280-automatically-running-queries-3), 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](https://meta.discourse.org/t/32566) 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](https://meta.discourse.org/t/32566?silent=true) tutorial series
We also have a dedicated #sql-tutorial series about the [Data Explorer](https://meta.discourse.org/t/32566)! 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](https://meta.discourse.org/t/data-explorer-tutorial-part-1-writing-your-first-query/277925)
- [Data Explorer Tutorial - Part 2 - Discourse SQL Basics](https://meta.discourse.org/t/data-explorer-tutorial-part-2-discourse-sql-basics/277927)
- [Utilizing Parameters in Data Explorer Queries](https://meta.discourse.org/t/utilizing-parameters-in-data-explorer-queries/277934)
- [Formatting Data Explorer Table Results](https://meta.discourse.org/t/formatting-data-explorer-table-results/277939)
> Last edited by @MarkDoerr 2026-02-25T00:39:12Z
>
> Last checked by @hugh 2025-09-03T00:45:21Z
>
> > **Check document**
> >
> > Perform check on document:
---
### Author:  [@HamMan2118](https://meta.discourse.org/u/HamMan2118)
#### Post date: [January 30, 2024, 11:50pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/272 "2024-01-30T23:50:16Z")
Hello there! đ
So I asked in [this thread](https://meta.discourse.org/t/any-way-to-select-all-topics-in-a-search-query/293178) on how to copy the links to all of the topics in the search query. I was directed here. So, I asked the manager of [our forum](https://forums.flightsimulator.com/) if we had this plugin installed which we do. However, we donât know how to use it to copy all of the links from a search query into a CSV file using this plugin. Has anyone done this action before and maybe point us in the right direction? đ
Note that I am a regular on the forum, I do not have staff powers. However, I am relaying this info back to the site manager who does who can do it for me. đ
Thanks in advance!
---
### Author:  [@Arkshine](https://meta.discourse.org/u/Arkshine)
#### Post date: [January 31, 2024, 1:39am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/273 "2024-01-31T01:39:23Z")
Hello Preston!
The [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin allows you to create SQL queries.
You want to create a new one that lists the topics you seek.
Then, when you run the query, you will see a CSV button to export the result into a CSV file.
I hope that helps!
Let us know if you need additional help creating the query!
---
### Author:  [@HamMan2118](https://meta.discourse.org/u/HamMan2118)
#### Post date: [January 31, 2024, 2:59am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/274 "2024-01-31T02:59:21Z")
Thanks! I will pass that on đ¤
---
### Author:  [@JammyDodger](https://meta.discourse.org/u/JammyDodger)
#### Post date: [July 8, 2024, 10:11am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/275 "2024-07-08T10:11:40Z")
3 posts were split to a new topic: [Working around the 10,000 result limit of data explorer?](https://meta.discourse.org/t/working-around-the-10-000-result-limit-of-data-explorer/315395)
---
### Author:  [@gormus](https://meta.discourse.org/u/gormus)
#### Post date: [September 28, 2024, 12:56pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/276 "2024-09-28T12:56:09Z")
Hi, I have a feature request for this great plugin.
Some of the reports I create using the [Data Explorer](https://meta.discourse.org/t/32566?silent=true), would be very helpful to my team if I could list them at the Dashboard Reports (`/admin/reports`) page.
Would it be possible to add an option to each [Data Explorer](https://meta.discourse.org/t/32566?silent=true) query to be listed at the Reports page? Thanks.
For example:
- List this custom query in the [Reports](https://meta.discourse.org/admin/reports) page
---
### Author:  [@kOS](https://meta.discourse.org/u/kOS)
#### Post date: [November 4, 2024, 9:07am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/277 "2024-11-04T09:07:09Z")
Thank you for this feature!
Iâm exploring how we could write custom metrics. We are using Discourse alongside Workadventu.re (a 2D Avatar Based A/V Collaborationtool).
Im running a custom script there to write actvities in a google sheet via a webhook. To have all things centralized in our heart tool (discourse), it would be great to have these data printed in discourseâŚ
Any Tipps?!
---
### Author:  [@davidgs](https://meta.discourse.org/u/davidgs)
#### Post date: [December 18, 2024, 4:55pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/278 "2024-12-18T16:55:38Z")
I was really hoping that there was a query builder that would help with writing SQL. My SQL is old and rusty and banging it out by hand is error-prone and time consuming. Sure would be nice if there was an easier way!
---
### Author:  [@Tris20](https://meta.discourse.org/u/Tris20)
#### Post date: [December 18, 2024, 5:03pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/279 "2024-12-18T17:03:56Z")
Hi @davidgs, welcome to Meta đ đ
> [@davidgs](#):
>
> I was really hoping that there was a query builder that would help with writing SQL.
Have you tried using an LLM like ChatGPT? It has reasonable understanding of Discourse and has been fairly reliable when I also needed to make [data explorer](https://meta.discourse.org/t/32566?silent=true) queries.
I often write my prompts like âWrite a Discourse [data explorer](https://meta.discourse.org/t/32566?silent=true) query which returns x,y,z from a,b,c where âŚâ and end up with something pretty reasonable.
In the top right of this page you might also see a little robot icon like below.
If thatâs the case you could also try the AI Helper Bot thatâs integrated with Meta
---
### Author:  [@Jagster](https://meta.discourse.org/u/Jagster)
#### Post date: [December 18, 2024, 5:32pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/280 "2024-12-18T17:32:59Z")
> [@Tris20](#):
>
> In the top right of this page you might also see a little robot icon like below
Most of us donât see it. But there is always [ask.discourse.com](http://ask.discourse.com)
---
### Author:  [@Tris20](https://meta.discourse.org/u/Tris20)
#### Post date: [December 18, 2024, 5:38pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/281 "2024-12-18T17:38:32Z")
> [@Jagster](#):
>
> Most of us donât see it. But there is always [ask.discourse.com](http://ask.discourse.com)
Yeah I couldnât remember if this was still just a trust level 3 thing or not. Good call on the [ask.discourse.com](http://ask.discourse.com) site.
As a reference example I threw `write a discourse data explorer query which returns a list of all users who said the word âsoftwareâ` into the text input bar of [ask.discourse.com](http://ask.discourse.com) and got the following query back:
```plaintext
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;
```
Pretty reasonable. I can also vouch for it doing significantly more complex queries than this
---
### Author:  [@GuidoD](https://meta.discourse.org/u/GuidoD)
#### Post date: [February 12, 2025, 8:26pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/282 "2025-02-12T20:26:53Z")
somehow the data export of the [data explorer](https://meta.discourse.org/t/32566?silent=true) is no longer working with the latest discourse and [data explorer](https://meta.discourse.org/t/32566?silent=true) updates that I installed about two hours ago. The data itself is created fine but can no longer be exported.
Clicking on the JSON or CSV buttons just creates a short flash and nothing else happened. It worked fine about two weeks ago.
In the webbrowser console I do see the following error:
CSV:
```plaintext
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:
```plaintext
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
```
---
### Author:  [@pento](https://meta.discourse.org/u/pento)
#### Post date: [February 14, 2025, 2:03am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/283 "2025-02-14T02:03:36Z")
Thanks for the bug report, @GuidoD!
I merged a fix for this issue earlier today, please try updating and confirm that it works for you. đ
[https://github.com/discourse/discourse-data-explorer/pull/359](https://github.com/discourse/discourse-data-explorer/pull/359)
---
### Author:  [@GuidoD](https://meta.discourse.org/u/GuidoD)
#### Post date: [February 15, 2025, 9:18am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/284 "2025-02-15T09:18:54Z")
> [@pento](#):
>
> please try updating and confirm that it works for you.
thanks for the quick fix.
I can confirm that it is working fine again.
---
### Author:  [@GuidoD](https://meta.discourse.org/u/GuidoD)
#### Post date: [February 19, 2025, 8:49am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/285 "2025-02-19T08:49:30Z")
for a small amount of data the export to JSON and CSV are both working fine. But for a bigger amount of data, the CSV export is working fine but the JSON export does run on nginx 502 bad gateway (probably due to a timeout after 30 sec where the JSON file creation does take longer than 30 sec).
Is there an easy way to enhance the nginx timeout value?
If I reduce the time frame of the query the csv file is 12.9 MB and the json file is 645 MB. The total csv file is 13.5 MB, so only a little bit bigger, but the json file is not created due to the nginx error.
Or maybe the JSON file creation is running on an error due to some strange data. Does the JSON file creation create a log file?
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [February 26, 2025, 11:28pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/286 "2025-02-26T23:28:54Z")
The JSON queries run with a higher outer LIMIT by default compared to the CSV queries. Add custom paging inside the query to ensure it completes on time.
---
### Author:  [@GregorSondermeier](https://meta.discourse.org/u/GregorSondermeier)
#### Post date: [March 26, 2025, 10:51am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/287 "2025-03-26T10:51:49Z")
Is it possible to execute UPDATE statements from the [Data Explorer](https://meta.discourse.org/t/32566?silent=true)?
Iâm trying to do a mass update of a certain table, but I get this error:
```plaintext
PG::FeatureNotSupported: ERROR: WITH query "query" does not have a RETURNING clause
LINE 29: ) SELECT * FROM query
```
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [March 26, 2025, 11:25am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/288 "2025-03-26T11:25:44Z")
No, [Data Explorer](https://meta.discourse.org/t/32566?silent=true) only executes read only transactions. (If you updated your query to have a RETURNING clause, you would get a read only transaction error.)
If youâre self hosting, use one of the âlauncher enterâ guides, like
> [@Edit a user preference for everyone or a subset of users](https://meta.discourse.org/t/edit-a-user-preference-for-everyone-or-a-subset-of-users/25162):
>
> bookmark This guide explains how to edit a user preference for everyone or a subset of users in Discourse. person_raising_hand Required user level: System Administrator warning 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âŚ
If you already have your UPDATE designed, then you can `psql discourse` instead of `rails c`.
If youâre on a hosted service, contact support to have them execute the change.
---
### Author:  [@GregorSondermeier](https://meta.discourse.org/u/GregorSondermeier)
#### Post date: [March 26, 2025, 11:35am UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/289 "2025-03-26T11:35:20Z")
Thanks for the reply. Makes sense. Weâre on a hosted service, so Iâll contact their support then.
---
### Author:  [@Naman\_Gala](https://meta.discourse.org/u/Naman_Gala)
#### Post date: [April 15, 2025, 10:05pm UTC](https://meta.discourse.org/t/discourse-data-explorer/32566/290 "2025-04-15T22:05:51Z")
If i share a SQL query with the moderatorâs group, how can the users in moderator group run the query from their account? They cant see the [Data Explorer](https://meta.discourse.org/t/32566?silent=true) plugin
[Next page](https://meta.discourse.org/t/discourse-data-explorer/32566.md?page=2)