Querying JSON data

In Propel, you can query JSON data directly. To access structured JSON data within a column, you can use JavaScript dot and bracket notation.

Example

For instance, suppose a column called payload has the following JSON structure:

{
  "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 inside the payload via payload.order_details.taco_count.

Important

In order to use any JSON column in filters when querying a Metric, you must add the JSON column as a dimension to the Metric. For example, payload would need to be added as a Metric dimension.

Querying JSON columns via the API

When using this notation via the API, it’s simply a case of adding the correct reference to the particular key you wish to use. For example, in a TimeSeries query with a filter applied:

query TimeSeriesExample1 (
  $timeRange: RelativeTimeRange,
  $granularity: TimeSeriesGranularity!,
  $tacocount: Int!
) {
    timeSeries ({
      metricName: "Revenue",
      timeRange: { relative: $timeRange }
      granularity: $granularity
      filters: [{
        column: "payload.order_details.taco_count"
        operator: GREATER_THAN
        value: $tacocount
      }]
    }) {
      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.

Validation

The JavaScript dot and bracket notation is validated to make sure the syntax is correct. It is not validated against the data stored in the JSON - this is because the reference can be created before the data exists.

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"].