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 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
- In this live 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.
- Unpack
payload.amountagain, 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.amountand Output mode to Unnest object. - RegEx's Pattern,
KB-([0-9]{5}), puts the digits intoref, and the Formula after it buildsbooking_id, VARCHAR, andpaid_eur, DOUBLE. - 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.
- Join's OUTPUT LEFT has 17 payments that matched no booking; read their
payload.note. - 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 putsKB-back in front. - The Summarize after Join shows 64
cancelledpayments, 2717 paid. Forcompleted,paidis 30 belowbooked;returnedbalances. - OUTPUT RIGHT holds 21 unpaid bookings: 4
completedworth 220 and 17no-show. - In the ledger, the bottom-left Manual Input, double-click the 0 in each
amount_eurcell so it's selected, and type over it:2717,-220and-30, then select the Summarize beside it.
Swipe sideways to see the canvas, or open this page on a larger screen to work with 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 and RegEx references. For later: 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.