At Propel, we’re dedicated to continually enhancing the types of data you can sync and the ways you can query that data, and today we are delighted to announce Propel’s support for JSON. Let’s take a look at the new features for JSON we’ve recently added.

Querying JSON data from Propel

JSON data is represented by the JSON type in Propel. JSON columns behave like any other column in Propel, but they support a special syntax for accessing nested properties. That’s why, when you access a JSON column from the Propel Console, it’s clearly marked as having type JSON:

<div class="alt-text">An animated screen capture of the Propel Console showing a query filter column select dropdown with a JSON column named “payload”.</div>

You can access nested properties of a JSON column using JavaScript dot and bracket notation. For example, assume we have a Data Pool with a JSON column named payload, and that payload contains the following object:

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

Using dot notation, we can access the customer ID using <span class="code-exp">payload.customer_id</span>, and we can access the taco using <span class="code-exp">payload.order_details.taco_count</span>. This syntax is supported throughout Propel’s Console. For example, we can use it to take the sum of ordered tacos:

We can use dot notation within GraphQL queries the same way. In the example below, we filter taco orders on status and total price. Learn more about queries via the GraphQL API.

query {
  timeSeries (input: {
    metricName: "taco_orders"
    granularity: FIFTEEN_MINUTES
    timeRange: { relative: LAST_N_DAYS n: 30 }
    filters: [{
      column: "payload.order_details.status"
      operator: EQUALS
      value: "ordered"
    } {
      column: "payload.order_details.total_price"
      operator: GREATER_THAN
      value: "10"
    }]
  }) {
    labels
    values
  }
}

Syncing JSON data to Propel

In order to use Propel’s new JSON functionality, you’ll need to start syncing JSON data to Propel.

AWS S3 & Parquet

Propel supports syncing JSON data from Parquet files stored in AWS S3. There are many ways to store JSON data in Parquet. The simplest way is store JSON data in Parquet is as a string; however, you can also store JSON data using groups of fields or Parquet’s logical <span class="code-exp">MAP</span> and <span class="code-exp">LIST</span> types. Propel supports all of the above, so you are free to choose what works best for you.

Learn more about syncing Parquet files from AWS S3 to Propel.

Snowflake

Snowflake supports multiple semi-structured data types such as <span class="code-exp">VARIANT</span>, <span class="code-exp">OBJECT</span>, and <span class="code-exp">ARRAY</span> types. These types can be mapped to <span class="code-exp">STRING</span> or <span class="code-exp">JSON</span> in Propel.

Learn more about using Snowflake with Propel.

Webhooks

Propel supports sending JSON data directly to Propel via HTTP POST requests. This functionality is currently in preview.

Get in touch with us if you’d like access to this preview functionality.

Conclusion

Propel supports JSON data from various sources, including Parquet files in AWS S3 data lakes, JSON payloads in webhooks, and semi-structured types in Snowflake. Once imported as JSON column types, you can query your data using metrics just like any other column type.

Further reading

• How to build a Snowflake API
• Propel UI Kit: Data visualization and dashboard React components
• 5-Minute demo: How to expose your Snowflake data via a blazing-fast GraphQL API

If you don’t have a Propel account yet, you can try Propel for free and start building data apps and customer-facing analytics.