---
title: "Extract JSON fields and reconcile payments | Oplema"
description: "Learn to unpack JSON payment records in Strata, pull booking numbers out of free text with RegEx, join them to the till and explain the gap to the cent."
url: "https://oplema.com/learn/many-sources/extract-json-and-reconcile"
updated: "2026-11-04"
---

Lesson 7 of 9 · Lab

# Extract fields from JSON and reconcile payments

JSON packs named fields into one text cell. The JSON block unpacks them and RegEx pulls a pattern out of free text. A reconciliation then explains every euro between two totals.

**Unnest object** turns each field of a JSON object into a column, such as `payload.amount`; a field holding another object needs a second unnest.

The card provider says [fictional Kramerica Bikes](https://oplema.com/learn/export-to-report/first-data-flow) took one total for September's web bookings, and the till says another; Marit wants the gap explained. Each of the 433 payments has a `payload` of JSON with a `method`, a `note` such as `Booking KB-10002 via web` and an `amount` object with `value` in cents. The lower **Manual Input** holds the 454 bookings whose `channel` is `web`, another column you dropped in the first course, Build a report from a CSV export.

Every note holds the booking number. Will a pattern for `KB-` and five digits find them all?

No. Some notes write `kb-`, and some say `Web order` with no prefix.

## Unpack the payments and close the gap

1. In this live [Strata](https://oplema.com/strata) flow (Oplema's browser app for data prep), set the first **JSON** block's **Output mode** to **Unnest object**. The second **JSON** block and the five after it stay red until both are set.
2. Unpack `payload.amount` again, so the cents get their own column, `payload.amount.value`. If you're stuck
   Drag from the first **JSON** block to the second. Select the second, set **JSON column** to `payload.amount` and **Output mode** to **Unnest object**.
3. **RegEx**'s **Pattern**, `KB-([0-9]{5})`, puts the digits into `ref`, and the **Formula** after it builds `booking_id`, VARCHAR, and `paid_eur`, DOUBLE.
4. The **Summarize** at the top right reads 433 payments and 19445.5; the one at the bottom right, 373 finished web hires and 16978.5.
5. **Join**'s **OUTPUT LEFT** has 17 payments that matched no booking; read their `payload.note`.
6. Change the pattern so it finds every booking number. Check: **OUTPUT LEFT** is empty. If you're stuck
   In **RegEx**, change **Pattern** to `[0-9]{5}`, then select the block. The Formula puts `KB-` back in front.
7. The **Summarize** after **Join** shows 64 `cancelled` payments, 2717 paid. For `completed`, `paid` is 30 below `booked`; `returned` balances.
8. **OUTPUT RIGHT** holds 21 unpaid bookings: 4 `completed` worth 220 and 17 `no-show`.
9. In the ledger, the bottom-left **Manual Input**, double-click the 0 in each `amount_eur` cell so it's selected, and type over it: `2717`, `-220` and `-30`, then select the **Summarize** beside it.

In an expression, press Esc to apply it and move on (press it twice if a suggestion list is open).

What the ledger should show

It reads 2467: €19,445.50 paid less €16,978.50 booked, and €2,717.00 − €220.00 − €30.00.

## Every euro of the gap has a reason

The 17 missed payments (8 `kb-`, €467.50; 9 `Web order`, €454.00) were errors in the reference, and the money was right.

The gap has three named reasons. Canceled web bookings were charged on confirmation, and nobody has refunded them since Arne retired: €2,717.00 owed back. Four finished hires were never paid (€220.00, Join's Right output), and six payments were €30.00 short in all. The 17 no-shows (€439.00 booked) were never charged.

Your line for Marit: "The provider is €2,467.00 above the till. It's €2,717.00 of refunds owed, less €220.00 never paid and €30.00 short."

See the [JSON](https://oplema.com/docs/strata/parse/json) and [RegEx references](https://oplema.com/docs/strata/parse/regex). For later: [Data quality](https://oplema.com/learn/business-intelligence/data-quality) explains why a gap needs a named reason.

## Common reconciliation mistakes

- **Testing a pattern on the first rows.** Only 4 of the first 100 notes don't say `KB-`, easy to miss in a preview.
- **Netting reasons off.** A refund owed and a missing payment partly cancel. Report each.
- **Forgetting the units.** The provider's value is in cents.

## JSON and patterns in SQL and Excel

```
SELECT  payment_id,
       payload ->> '$.note'  AS  note,
       (payload ->> '$.amount.value' ):: INTEGER  /  100  AS  paid_eur,
       'KB-'  ||  regexp_extract(payload ->> '$.note' ,  '[0-9]{5}' )  AS  booking_id
FROM  payments;
```

`->>` reads a path as text, as the JSON blocks do. In Excel, give the note its own column with Power Query's **Parse > JSON** before `=REGEXEXTRACT(D2, "[0-9]{5}")`; on the whole payload, the pattern can catch the amount's digits.
