OrientDB SQL - How to unwind multiple attributes from an inline collection

Question

Suppose there is a document with an inline collection of objects, as in the example below:

{ "name": "Alice", 
  "children": [{ "name": "Bob", "age": 3 }, { "name": "Charlie", "age": 7 }] 
}

I'd like to obtain one row per item in the collection, and each inner attribute as a separate column, such as this:

| name    | childName | childAge |
| Alice   | Bob       | 3        |
| Alice   | Charlie   | 7        |

I tried the following query, but it generates a Cartesian product:

select name, children.name as childName, children.age as childAge
from Employee
unwind childName, childAge

I've also managed to extract each child with the expand and flatten functions, but without any reference to its parent.

Answer 1

The easiest thing you can do is as follows:

  SELECT name, children.name as childName, children.age as childAge FROM (
    SELECT name, children FROM Employee UNWIND children
  )