Last Updated Oct 30, 2021

How to use the queries tool to run ad-hoc sql queries

by JR · 7 minute read

What is the queries tool?

The queries tool exposes a fully supported SQL interface to query your events. In the backend we use Clickhouse as our data store, and give you full access to query your data using their SQL reference https://clickhouse.com/docs/en/sql-reference/.

What are some example queries to get me started?

The following will basically show you what we store behind the scenes at GraphJSON

SELECT
  *
FROM
  all_events
LIMIT
  1

You can construct more complex things. Imagine you were Uber rides, and each ride had a day of week field. You could see how many rides on each day with the following query

SELECT
  COUNT(*) as count,
  JSONExtractString(json, 'Day_Of_Week') as day
FROM
  all_events
GROUP BY
  day
LIMIT
  1

You can also do joins! For instance you might run the following query to see which riders took a ride on both Saturday and Sunday

SELECT
  *
FROM
  (
    SELECT
       *
    FROM
       all_events
    WHERE
       JSONExtractString(json, 'Day_Of_Week') = 'Saturday'
  ) AS a
JOIN
  (
    SELECT
      *
    FROM
      all_events
    WHERE
      JSONExtractString(json, 'Day_Of_Week') = 'Sunday'
  ) AS b
ON 
  JSONExtractString(a.json, 'Rider_Id') =
    JSONExtractString(b.json, 'Rider_Id')
LIMIT
  1

If you want to do some more advanced things. I would recommend reading the following followup guide https://www.graphjson.com/guides/queries-visualize

GraphJSON by @TheBuilderJR

© GraphJSON 2021