Handle JSON type in MySQL

Question

I've connected my MySQL data source. Some columns are JSON, with nested objects or arrays inside. Holistics treats each one as a single field, so the inner values don't show up as dimensions. How do I expose them in models and datasets?

Answer

The data we'll work with

Throughout this guide we'll use one table, customer_orders, with two JSON columns - customer and items. One example row:

order_id	order_date	customer	items
1001	2026-05-04	{"id": 101, "name": "Alice", "vip": true, "address": {"city": "Chicago"}}	[{"product_id": "P001", "name": "Mouse", "quantity": 2, "price": 15.00}, {"product_id": "P002", "name": "Keyboard", "quantity": 1, "price": 45.00}]

What we want in the report builder:

• Customer info as order attributes. Customer fields like id, name, is_vip, and city should be selectable as dimensions directly on the orders table - one value per order, just like order_date.
• One row per item. Each item in the items array becomes its own row so we can group by product and aggregate quantities or revenue.

The end result is two flat tables:

customer_orders

order_id	order_date	customer_id	customer_name	customer_is_vip	customer_city
1001	2026-05-04	101	Alice	true	Chicago

order_items

order_id	product_id	product_name	quantity	price	revenue
1001	P001	Mouse	2	15.00	30.00
1001	P002	Keyboard	1	45.00	45.00

Rule of thumb

Look at the JSON shape. If a column holds a single object (like customer), extract its fields as extra columns on the same row. If a column holds an array (like items), flatten it into a separate table with one row per element.

---

Scenario 1: Object or scalar - extract inline

Why this works

The JSON value on each row is a single object or scalar, so you can read a field out with a JSON path expression inside the dimension. The extraction happens at query time every time the field is used.

Option A: SQL passthrough in a dataset

Quick and ad-hoc - no model changes needed. Use SQL passthrough to extract fields directly in the dataset:

sql_number('JSON_VALUE', customer_orders.customer, '$.id')           // customer_id
sql_text  ('JSON_VALUE', customer_orders.customer, '$.name')         // customer_name
sql_truefalse('JSON_VALUE', customer_orders.customer, '$.vip')       // is_vip
sql_text  ('JSON_VALUE', customer_orders.customer, '$.address.city') // city

Option B: Model dimension

Reusable across datasets. Define the extraction once in the model:

dimension customer_name {
  label: "Customer Name"
  type: "text"
  definition: @sql {{ #SOURCE.customer }} ->> '$.name' ;;
}

dimension is_vip {
  label: "Is VIP"
  type: "truefalse"
  definition: @sql {{ #SOURCE.customer }} -> '$.vip' ;;
}

MySQL path operators

• ->> returns unquoted text - use for strings.
• -> returns a JSON value - use for booleans, numbers, or to chain (e.g. customer -> '$.address' ->> '$.city').

---

Scenario 2: Array - flatten with SQL

Why inline won't work

items holds many values per row, so it can't map to a single dimension. Instead, flatten it into a separate table where each element gets its own row, then join it back to orders.

How

Create a query model (or a view on MySQL) using JSON_TABLE:

SELECT
  co.order_id,
  co.order_date,
  i.product_id,
  i.product_name,
  i.quantity,
  i.price,
  i.quantity * i.price AS revenue
FROM {{ #customer_orders }} co
CROSS JOIN JSON_TABLE(
  co.items,
  '$[*]' COLUMNS (
    product_id   VARCHAR(20)    PATH '$.product_id',
    product_name VARCHAR(100)   PATH '$.name',
    quantity     INT            PATH '$.quantity',
    price        DECIMAL(10, 2) PATH '$.price'
  )
) AS i;

Now both tables from the target schema are in place - order-level dimensions and item-level breakdowns live in the same dataset.

---

Version & dialect caveats

• JSON_VALUE requires MySQL 8.0.21+. On older versions, use JSON_EXTRACT instead, but note it returns values in JSON form - strings come back quoted ("Alice" rather than Alice). Wrap with JSON_UNQUOTE:

  sql_text('JSON_UNQUOTE', sql_text('JSON_EXTRACT', customer_orders.customer, '$.name'))

• JSON_TABLE requires MySQL 8.0+. On older versions, flatten arrays with JSON_EXTRACT + JSON_LENGTH, or move the unnesting into your ETL.