OrientDB has a powerful way to extract portion from document field. This applies to Java API, SQL Where conditions and SQL projections.
To extract parts you have to use the square brackets.
Example: tags is a EMBEDDEDSET of strings containing the values ['Smart', 'Geek', 'Cool'].
The expression tags[0] will return 'Smart'.
Inside square brackets put the items separated by comma ",".
Following the tags example above, the expression tags[0,2] will return a list with [Smart, 'Cool'].
Inside square brackets put the item lower and upper bounds separated by "-".
Following the tags example above, the expression tags[1-2] returns ['Geek', 'Cool'].
Example:
SELECT * FROM profile WHERE phones['home'] like '+39%'
The same is using double quotes.
You can go in chain (contacts is a map of map):
SELECT * FROM profile WHERE contacts[phones][home] like '+39%'
With List and arrays you can pick an item of a range of its:
SELECT * FROM profile WHERE tags[0] = 'smart'
and single items:
SELECT * FROM profile WHERE tags[0,3,5] CONTAINSALL ['smart', 'new', 'crazy']
and a range of items:
SELECT * FROM profile WHERE tags[0-5] CONTAINSALL ['smart', 'new', 'crazy']
Inside the square brackets you can specify a condition. Today only equals condition is supported.
Example:
employees[label = 'Ferrari']
You can cross the graph using the projection. This an example traverse all the retrieved nodes with name "Tom". "out" is outEdges and it's a collection. Until now collection couldn't be traversed with the . notation. Example:
SELECT out.in FROM v WHERE name = 'Tom'
This retrieves all the vertexes connected to the outgoing edges of the Vertices with name = 'Tom'.
Collection can be filtered with the equals operator. This an example traverse all the retrieved nodes with name "Tom". The traversal cross the out edges but only where the linked (in) Vertex has label "Ferrari" and then forward to the:
SELECT out[in.label = 'Ferrari'] FROM v WHERE name = 'Tom'
Or selecting vertexes nodes based on class:
SELECT out[in.@class = 'Car'] FROM v WHERE name = 'Tom'
Or both:
SELECT out[label='drives'][in.@class = 'Car'] FROM v WHERE name = 'Tom'
As you can see where multiple follows multiple [the result set is filtered in each step like a Pipeline.
NOTE: This doesn't replace the support of GREMLIN. GREMLIN is much more powerful because it does thousands things more, but it's a simple and, at the same time, powerful tool to traverse relationships.
In the future you could use the full expression of OrientDB SQL language inside the squared brackets [], like:
SELECT out[in.label.trim() = 'Ferrari' AND in.@class='Vehicle'] FROM v WHERE name = 'Tom'
But for this you have to wait yet :-) Monitor the issue: https://github.com/nuvolabase/orientdb/issues/513