JSON text can contain nested objects and arrays. get_json_object extracts a path from JSON text. from_json parses text using an explicit schema, which lets later expressions refer to typed fields. explode turns an array into multiple rows, so its output grain is an element rather than an order.
Aster Supply stores line items on each order, and Blue Heron Rides stores event details in payload. Keep the parent id when expanding repeated values so you can trace the resulting rows back. An empty array produces no element rows under a normal explode; use the appropriate outer behavior when parents with no elements must survive. collect_list gathers values into an array but does not promise a business ordering by itself. PIVOT reshapes categories into columns. Inspect NULLs and duplicates before using either operation in a report.