LATERAL allows a table function to read values from the row to its left. FLATTEN uses that link to emit one row per array element while retaining the source document's columns. Always carry a document identifier through the expansion. File names can repeat across documents, so the parsed order key is often the safer business key.
The INDEX column is zero based; VALUE holds the element as VARIANT. OUTER => TRUE keeps a source row when its array is empty or absent, with NULL flattened values. That means COUNT(*) and COUNT(f.value) measure different things after an outer expansion. One counts the preserved row, while the other counts populated elements. Aggregate back to the requested document grain before joining another detail array.