CODEWITHSUNDEEP
orientdb
GFonte
GFonte

Reputation: 451

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

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.

Upvotes: 0

Views: 314

Answers (2)

Luigi Dell'Aquila
Luigi Dell'Aquila

Reputation: 2814

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
  )

Upvotes: 2

Will
Will

Reputation: 21

I want to do the same thing.

The closest I can find in ODB is separating the children into vertices like in this question: OrientDB: How to flatten nested heirarchy into a single record

I would prefer to do it how mongo handles it (Not exactly what I want, but the principal is there.): MongoDB unwind multiple arrays

Failed attempts:

  1. select name, children from Test UNWIND children result from query 1
  2. select value.name, value.age from (select expand(children) from Test) result from query 2 If there was a link to the parent this would work, but value.$parent returns empty.

Upvotes: 1

Related Questions