Pack columns into a nested value

CREATE TEMP TABLE items AS SELECT * FROM (VALUES
  (1, 'Alice Ng', 120.00, 'north', 'widget'), (2, 'Tom & <Jerry>', NULL, NULL, 'gizmo')
) t(id, customer, amount, region, product);
-- each text is escaped (& first, then < and >) before it is put between tags; a missing value gives an empty tag
CREATE MACRO esc(x) AS replace(replace(replace(x, '&', '&amp;'), '<', '&lt;'), '>', '&gt;');
CREATE TEMP TABLE result AS
SELECT id,
       {'customer': customer, 'amount': amount} AS as_struct,
       [region, product] AS as_list,
       to_json({'customer': customer, 'amount': amount}) AS as_json,
       '<row><customer>' || coalesce(esc(customer), '') || '</customer><amount>' || coalesce(amount::VARCHAR, '') || '</amount></row>' AS as_xml
FROM items;
SELECT * FROM result ORDER BY id;
DESCRIBE result;
SELECT CASE WHEN ((SELECT list(column_name) FROM (DESCRIBE result)) = ['id', 'as_struct', 'as_list', 'as_json', 'as_xml'] AND (SELECT as_struct.customer || '/' || as_struct.amount FROM result WHERE id = 1) = 'Alice Ng/120.00' AND (SELECT as_list FROM result WHERE id = 2) = [NULL, 'gizmo'] AND (SELECT as_json::VARCHAR FROM result WHERE id = 2) = '{"customer":"Tom & <Jerry>","amount":null}' AND (SELECT as_xml FROM result WHERE id = 2) = '<row><customer>Tom &amp; &lt;Jerry&gt;</customer><amount></amount></row>' AND (SELECT as_xml FROM result WHERE id = 1) = '<row><customer>Alice Ng</customer><amount>120.00</amount></row>') THEN 'as expected' ELSE error('customer and amount were not packed into one struct, one JSON and one escaped XML column, or region and product into one list') END AS verdict;

-- Avro: the packed struct is written to an Avro file and read back as one column
COPY (SELECT id, as_struct FROM result) TO '<your_scratch_dir>/packed.avro' (FORMAT avro);
SELECT CASE WHEN ((SELECT count(*) FROM read_avro('<your_scratch_dir>/packed.avro') WHERE as_struct.customer = 'Tom & <Jerry>' AND as_struct.amount IS NULL) = 1 AND (SELECT count(*) FROM read_avro('<your_scratch_dir>/packed.avro')) = 2) THEN 'as expected' ELSE error('the packed struct did not come back from the Avro file as one column') END AS verdict;