OrientDB: improved SQL filtering

London, December 19th 2013

The more developers use OrientDB, the more use case we receive. One of the most wanted requirements is improving filtering of results, specially on traversing.

The OrientDB SQL dialect is powerful enough to mix SELECT and TRAVERSE to create very complex queries, but sometimes such queries are pretty big and hard to read.

For this reason we developed the pipeline filtering using brackets, by extending current SQL engine.

Examples

Example 1: Get the first friend of mine

You can get it by using:

select expand( out ) from (

  select out('Friend') from Profile where name = 'Luca'

) limit 1

     

But now you can also do this:

select out('Friend')[0] from Profile where name = 'Luca'

Much easier and faster on execution!

Example 2: from GratefulDeadConcerts database traverse all the out() connection and get the first song called 'MONA'

select from (

  select expand( out() ) from V

) where name = 'MONA' and type = 'song' limit 1

@rid	@version	@class	name	in_followed_by	song_type	performances	type	out_followed_by	out_sung_by	out_written_by
#9:6	19	V	MONA	[2]	cover	1	song	[2]	[1]	[1]

Executed in 0.455 seconds.

Now let's use the new way:

select expand( out()[name='MONA'][type='song'] ) from V

@rid	@version	@class	name	in_followed_by	song_type	performances	type	out_followed_by	out_sung_by	out_written_by
#9:6	19	V	MONA	[2]	cover	1	song	[2]	[1]	[1]

Executed in 0.106 seconds.

Note last execution, in this case, is 4x faster!

Squared brackets [] allow to:

- filtering by one index, example out()[0]

- filtering by multiple indexes, example out()[0,2,4]

- filtering by ranges, example out()[0-9]

- filtering by equal conditions (only equals is supported), example out()[@class = 'Person']

Brackets [] can be chained, in this case it's like filtering by AND operator. Look at the Example 2.

For more information about the issue: https://github.com/orientechnologies/orientdb/issues/1888

This is available in "develop" branch right now and will be part of next release.

Luca Garulli

CEO at Orient Technologies

the Company behind OrientDB

http://about.me/luca.garulli