OrientDB Manual 1.7.8

Work with fields

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.

Extract punctual items

Single item

Example: tags is a EMBEDDEDSED of strings containing the values ['Smart', 'Geek', 'Cool'].

The expression tags[0] will return 'Smart'.

Single items

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'].

Range items

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'].

Usage in SQL query

Example:

SELECT * FROM profile WHERE phones[home] like '+39%'

The same is using quotes:

phones[and phones["home"]('home'].md)

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']

Condition

Inside the square brackets you can specify a condition. Today only equals condition is supported.

Example:

employees[label = 'Ferrari']

Use in graphs

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[email protected] = 'Car'] FROM v WHERE name = 'Tom'

Or both:

SELECT out[label='drives'][in[email protected] = '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.

Future directions

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[email protected]='Vehicle'] FROM v WHERE name = 'Tom'

But for this you have to wait yet :-) Monitor the issue: https://github.com/nuvolabase/orientdb/issues/513