ARRAY: Ordered Collections

Concepts: dmArrayType

Storing Lists Inside a Row An ARRAY column stores an ordered list of values inside a single row. A product can have an array of tags: ['electronics', 'sale', 'clearance']. An order can have an array of line item STRUCTs. ARRAYs eliminate the need for a separate junction or child table for simple one-to-many relationships. To query individual elements of an ARRAY, you use UNNEST. UNNEST flattens the array into rows, producing one output row per array element. This is powerful but dangerous: if you UNNEST and then aggregate without accounting for the multiplication, your metrics inflate. The UNNEST Trap A product has 3 tags and costs $100. If you UNNEST the tags array and then SUM(price), you get $300 instead of $100. The UNNEST multiplied the product row by 3 (one per tag), and the SUM coun