Working With Shopify Data Using Postgres JSONB

TomShopifyLeave a Comment

This post is part of a series following my building of the Best Sellers app to help Shopify stores manage and promote best selling products. If you'd like to start at the beginning of the series, you can start here.

My focus this week is on the best way to store and manage data for my upcoming Best-Sellers Shopify app.

Shopify uses JSON to transport data to and from the Shopify API. My go to database these days is Postgres, which also has some great tools for storing and working with JSON data. This means Shopify and Postgres effectively “speak the same language”.

In this post, I’ll walk you through how I will be using Postgres’s built-in JSONB data type to store and manage data for my upcoming Best-Sellers Shopify app.

This post will include:

  • Shopify and JSON
  • Postgres and JSON
  • Shopify JSON and Postgres JSONB – the perfect match
  • An Example Shopify Order schema using JSONB
  • Querying Shopify Order data with JSONB

Shopify and JSON

Shopify uses JSON (Javascript Object Notation) as the means of sending and receiving data throughout the Shopify API.

You can see some example Order JSON in last week’s A Day in the Life of a Shopify Order post.

Postgres and JSON

The Postgres database has support for working with JSON via two different data types: json and jsonb:

  • json – stores and exact copy of the document, preserving whitespace. This is faster to save than jsonb, but much more inefficient at queries.
  • jsonb – stores a compressed binary copy of the document. This makes it slower to update than json, and whitespace is not preserved, but queries should run much faster.

Shopify JSON and Postgres JSONB – the perfect match

Shopify sends all of its API and Webhook data via JSON. Postgres supports tools for storing and working with JSON data. This means we can pass Shopify JSON data directly to Postgres without modification.

For my Best-Sellers app, I opted to choose JSONB over JSON since I really need some of the advanced querying capabilities provided by JSONB and I’m ok with the drawbacks such as slower updates and lack of whitespace.

An Example Shopify Order schema using JSONB

To demonstrate how to work with Shopify data using Postgres’s JSONB data type, I’m going to show you a simple database schema that is very similar to the one I will be using for my app. Then in the next section of this post, I’ll demonstrate a few queries for working with the JSONB data.

Here is an example Rails migration for a Shopify Orders table:

And here is the same Orders table in SQL:

A few notes about the schema:

  • shopify_order_id is pulled out of the order data so I can add a unique contraint. This will prevent duplicate orders from creeping into my database
  • order_data is the JSONB column which stores the entire Order JSON for an order.
  • The order_data uses a GIN index to make querying the JSONB data easier.

Querying Shopify Order data with JSONB

Now let’s walk through a few examples so you can see how you can query this Order data stored as JSONB.

Select a count of all orders by financial_status

  • This example uses the -> operator to extract the financial_status from the order_data JSONB.
  • The financial_status is then used to group the results.

Select a count of all orders between two dates

  • The processed_at timestamp is used as the time the order was actually placed. We can’t trust the created_at timestamp because orders can be created after the fact by the Shopify API.
  • We must convert the processed_at to a timestamp with timezone before we can use it in a date query like so: (o.order_data->>'processed_at')::timestamptz

Select a sum of all line item quantities and prices, by product_id

  • We use the jsonb_array_elements Postgres function to extract each line_item from the line_items array.
  • The ->> JSONB operator is used to convert the value of quantity and price to text so we can convert them to their proper format as discussed in the following bullet point.
  • Then we convert the quantity to int: (li->>'quantity')::int, and price to numeric: (li->>'price')::numeric to allow us to SUM them.

Signup to be notified about future Shopify App development articles

Get Shopify App development tips and tricks straight to your inbox.

Summary

Shopify and Postgres make a powerful combo. Since both Shopify and Postgres are able to work directly with JSON, there is no need to modify the data. This means nothing should get lost in translation when you are storing data from Shopify for an app.

Postgres provides some powerful tools for storing and querying JSON data. In particular, the JSONB data type provides both power and flexibility to manage JSON data within your app.

If you have any specific questions or insights about working with Shopify, Postgres and JSON let me know in the comments or via the contact form.

Leave a Reply

Your email address will not be published. Required fields are marked *