Thursday 19 December 2013

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@classnamein_followed_bysong_typeperformancestypeout_followed_byout_sung_byout_written_by
#9:619VMONA[2]cover1song[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@classnamein_followed_bysong_typeperformancestypeout_followed_byout_sung_byout_written_by
#9:619VMONA[2]cover1song[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

No comments:

Post a Comment

Note: only a member of this blog may post a comment.