Parsing a Nested JSON Feed Without Exploding Rows
An analyst ingests a JSON API response where each record has an order_id, a customer object, and an items array of 1–50 line items, each with sku, qty, and price. The response is about 300 MB. The analyst uses a JSON Parse tool, which produces one row per top-level record with the items array stored as a JSON string. A colleague suggests using a Text to Columns tool with a comma delimiter to split the array. The analyst needs one row per line item for a downstream aggregation by SKU, and also needs the order-level customer attributes carried onto each line.
1. Why is Text to Columns on the items array the wrong tool, and what is the correct approach?
Read the worked answer
Text to Columns splits a single string into multiple columns (or rows) on a delimiter, but JSON arrays contain nested objects with their own commas, quotes, and braces. Splitting on commas will fragment {"sku":"A","qty":2,"price":9.99} into pieces that no longer parse, and the number of resulting columns varies per row (1–50 items), which Text to Columns cannot handle gracefully. The correct approach is to serialize the array back to a JSON string if needed and use the JSON Parse tool configured to output the array as rows, or use a combination of JSON Parse followed by a Select/RecordID and a Cross Tab/Transpose pattern. In practice: parse the top-level record, keep order_id and customer fields, then for the items array use a JSON Parse with the array path set to output rows (one row per element), producing sku, qty, price columns. Join back to the order-level fields on order_id (or carry them via a RecordID and a Join). The result is one row per line item with order attributes intact. If the JSON Parse tool cannot flatten the array directly in the installed version, use a Formula to extract the array string and a batch macro that parses each element, but the JSON Parse row-output option is the intended path.
2. The 300 MB file causes the workflow to run out of memory. What changes reduce memory pressure without losing line-item granularity?
Read the worked answer
Avoid materializing the whole 300 MB parsed structure at once. Options: (1) Use the JSON Parse tool in streaming/row mode and immediately Select only the fields needed (order_id, sku, qty, price, customer attributes), dropping the raw JSON string column as early as possible-large string columns dominate memory. (2) Filter to the period of interest before parsing if the API supports it. (3) If the file must be processed whole, split it into chunks (e.g., by order_id range) and process with a batch macro, writing partial outputs and unioning at the end. (4) Ensure the JSON Parse output is not sorted unnecessarily and that no Browse tools are attached to the large stream (Browse caches data). (5) Use AMP engine settings appropriately and avoid blocking tools (Sort, Join) on the full 300 MB stream until after the row expansion is reduced. The key principle: reduce column width and row count as early as possible, and never attach a Browse to a 300 MB stream.