Skip to main content

Response JSONata

Note API Connector now includes a built-in JSONata editor alongside JMESPath. JSONata is a flexible query and transformation language for JSON that not only extracts and filters data but also lets you calculate values, transform text, and restructure objects.

Why JSONata?

JMESPath is great for filtering and selecting fields, but JSONata gives you:

🧮 Math & Derived Fields – add new calculated values.
🔤 String Functions – join, split, uppercase, regex replace.
📅 Date & Time Handling – convert timestamps to readable dates.
📊 Aggregations – sum, average, min, max, counts.
🗂️ Restructuring – turn arrays into objects, flatten nested data.
⚡ Conditionals & Logic – apply if/else rules directly in the transformation.

tip

Paste the copied JSON into an AI assistant (like ChatGPT, Claude, Gemini) and describe the tabular transformation you need.

For example, you can ask:

“From this data, make a JSONata query that returns an array of objects with id and subscription_id fields.”

This ensures your query produces a tabular output (array of objects) that maps cleanly into Notion.

Using the JSONata Editor​

1️⃣ Open the Response Field Mapping view.
2️⃣ Go to Transform API Response → JSONata.
3️⃣ In the editor, type your JSONata expression and instantly preview the transformed result.

JSONata Editor

Example 1: Flatten parent→items into line‑item rows (carry parent fields)​

Convert nested orders into individual rows so each product is a separate row in Notion, while keeping order/customer info attached.

{
  "orders": [
    {
      "id": "O-1001",
      "customer": { "name": "Acme Co" },
      "items": [
        { "sku": "A1", "name": "Laptop", "qty": 2, "price": "$1,099.00" },
        { "sku": "C1", "name": "Cable", "qty": 3, "price": "$9.99" }
      ]
    },
    {
      "id": "O-1002",
      "customer": { "name": "Beta LLC" },
      "items": [{ "sku": "B2", "name": "Phone", "qty": 1, "price": "$699.00" }]
    }
  ]
}

JSONata

orders@$o.items.{
  "order_id": $o.id,
  "customer": $o.customer.name,
  "sku": sku,
  "item_name": name,
  "qty": qty,
  "unit_price": $number($replace(price, /[^0-9.]/, ""))
}
Copy-paste prompt for AI to get this JSONata

Write a JSONata expression that uses the orders array and flattens each order’s items into separate rows while carrying parent fields. Return an array of objects with exactly these fields: order_id, customer, sku, item_name, qty, unit_price. Parse price strings into numbers.

JSON: <PASTE YOUR ORDERS JSON HERE>

Output

[
  {
    "order_id": "O-1001",
    "customer": "Acme Co",
    "sku": "A1",
    "item_name": "Laptop",
    "qty": 2,
    "unit_price": 1099
  },
  {
    "order_id": "O-1001",
    "customer": "Acme Co",
    "sku": "C1",
    "item_name": "Cable",
    "qty": 3,
    "unit_price": 9.99
  },
  {
    "order_id": "O-1002",
    "customer": "Beta LLC",
    "sku": "B2",
    "item_name": "Phone",
    "qty": 1,
    "unit_price": 699
  }
]

Example 2: Turn an object keyed by date/ID into rows​

APIs often return “maps” keyed by date or ID. This turns them into clean rows for Notion.

Input

{
  "stats": {
    "2025-08-01": { "views": 120, "clicks": 6 },
    "2025-08-02": { "views": 150, "clicks": 9 }
  }
}

JSONata

$keys(stats).{
  "date": $,
  "views": stats[$].views,
  "clicks": stats[$].clicks
}
Copy-paste prompt for AI to get this JSONata

Write a JSONata expression that turns the stats object (keys are dates) into an array of objects with exactly these fields: date, views, clicks.

JSON: <PASTE YOUR STATS JSON HERE>

Output

[
  { "date": "2025-08-01", "views": 120, "clicks": 6 },
  { "date": "2025-08-02", "views": 150, "clicks": 9 }
]

Example 3: Spread dynamic key/value pairs into stable columns​

Some APIs send custom fields as an array of {key, value}. This spreads them into real columns.

Input

{
  "contacts": [
    {
      "id": 1,
      "name": "Alice",
      "custom_fields": [
        { "key": "twitter", "value": "@alice" },
        { "key": "plan", "value": "Pro" }
      ]
    },
    {
      "id": 2,
      "name": "Bob",
      "custom_fields": [
        { "key": "twitter", "value": "@bob" },
        { "key": "plan", "value": "Free" }
      ]
    }
  ]
}

JSONata

contacts.(
  $extras := $reduce(custom_fields, {}, function($o, $v) { $merge([$o, { ($v.key): $v.value }]) });
  $merge([ { "id": id, "name": name }, $extras ])
)
Copy-paste prompt for AI to get this JSONata

Write a JSONata expression that uses the contacts array and returns an array of objects with id, name, plus each {key, value} in custom_fields as top-level columns.

JSON: <PASTE YOUR CONTACTS JSON HERE>

Output

[
  { "id": 1, "name": "Alice", "twitter": "@alice", "plan": "Pro" },
  { "id": 2, "name": "Bob", "twitter": "@bob", "plan": "Free" }
]

Example 4: Normalize nested attributes into columns + clean strings​

Flatten nested attributes into columns and normalize weights into consistent kg values.

Input

{
  "products": [
    {
      "id": "A1",
      "title": "Laptop",
      "attributes": { "color": "Space Gray", "size": "13-inch" },
      "weight": "1.4 kg"
    },
    {
      "id": "B2",
      "title": "Phone",
      "attributes": { "color": "Black", "size": "6.1-inch" },
      "weight": "173 g"
    }
  ]
}

JSONata

products.{
  "id": id,
  "name": title,
  "color": attributes.color,
  "size": attributes.size,
  "weight_kg": $number(
    $contains(weight, "kg") ? $replace(weight, /[^0-9.]/, "") :
    $contains(weight, "g")  ? ($replace(weight, /[^0-9.]/, "") / 1000) :
    ""
  )
}
Copy-paste prompt for AI to get this JSONata

Write a JSONata expression that uses the products array and returns an array of objects with exactly these fields: id, name (rename title to name), color (from attributes.color), size (from attributes.size), and weight_kg as a number. Normalize weight strings like "1.4 kg" or "173 g" into kilograms.

JSON: <PASTE YOUR PRODUCTS JSON HERE>

Output

[
  {
    "id": "A1",
    "name": "Laptop",
    "color": "Space Gray",
    "size": "13-inch",
    "weight_kg": 1.4
  },
  {
    "id": "B2",
    "name": "Phone",
    "color": "Black",
    "size": "6.1-inch",
    "weight_kg": 0.173
  }
]

Example 5: Convert epoch timestamps to ISO strings for Notion Date​

APIs often send timestamps as milliseconds since 1970. This converts them into Notion-friendly date strings.

Input

{
  "events": [
    { "id": 1, "created_ms": 1723200000000 },
    { "id": 2, "created_ms": 1725888400000 }
  ]
}

JSONata

events.{
  "id": id,
  "created_at": $fromMillis(created_ms, "[Y0001]-[M01]-[D01]T[H01]:[m01]:[s01]Z")
}
Copy-paste prompt for AI to get this JSONata

Write a JSONata expression that uses the events array and returns an array of objects with id and created_at, where created_at is created_ms converted from epoch milliseconds to an ISO 8601 string ending with Z.

JSON: <PASTE YOUR EVENTS JSON HERE>

Output

[
  { "id": 1, "created_at": "2024-08-09T00:00:00Z" },
  { "id": 2, "created_at": "2024-09-09T09:26:40Z" }
]

Example 6: Keep a stable schema (defaults for missing fields)​

Ensures every row has the same set of fields, even if the API is missing some values.

Input

{
  "contacts": [
    { "id": 1, "name": "Alice", "email": "[email protected]" },
    { "id": 2, "name": "Bob" }
  ]
}

JSONata

contacts.{
  "id": id,
  "name": name,
  "email": email ? email : ""
}
Copy-paste prompt for AI to get this JSONata

Write a JSONata expression that uses the contacts array and returns an array of objects with exactly these fields: id, name, email. If email is missing, set it to an empty string.

JSON: <PASTE YOUR CONTACTS JSON HERE>

Output

[
  { "id": 1, "name": "Alice", "email": "[email protected]" },
  { "id": 2, "name": "Bob", "email": "" }
]

Summary​

JSONata goes beyond simple extraction. It enables you to calculate, reshape, and enrich your data before sending it into Notion, making your workflows more powerful and automated.

Leave a Comment