Querying JSON data

Query JSON data using dot and bracket notation to access nested fields.

​

Example JSON

Let’s suppose you have a JSON column payload structured like this:

{
  "customer_id": 5,
  "order_id": 34,
  "store_id": 4445,
  "order_details": {
    "taco_count": 5,
    "total_price": 30.4,
    "checkout_time": "2022-08-01T09:03:32Z"
  },
  "created_at": "2022-08-01T09:02:15Z"
}

You can reference the taco_count value inside the payload via the following syntax:

payload.order_details.taco_count

​

Querying JSON columns via the API

Use dot notation in API queries to reference specific JSON keys. Here’s a TimeSeries query example with filtering:

query TimeSeriesExample1 {
  timeSeries ({
    ...
    filterSql: "payload.order_details.taco_count > 2"
    ...
  }) {
    values
  }
}

The filter uses payload.order_details.taco_count to refer to the taco count key within payload.order_details.

​

Using JSON in the console

When creating a Metric in the Console, if a column is of type JSON, it will be clearly marked. If a JSON column is selected, the text field allows for JavaScript dot and bracket notation to be entered to reference nested values.

​

Property escaping

If you have a column named “foo.bar”, then you cannot reference it using foo.bar, since this will be interpreted as JavaScript dot notation. Instead, you can use square brackets to escape the period, like this: ["foo.bar"].