Working with Fields

When you query OrientDB it normally returns complete fields. In the event that you would like to only retrieve certain parts of the Document field, you can extract them through the Java API, 'WHERE' conditions and SQL projections.

To extract parts, use the brackets.

Extracting Punctual Items

Under normal operations, when you issue a query to OrientDB the return value contains complete fields. That is, if you issue SELECT against a certain property, what you receive is the complete value on that property. For instance,

orientdb> SELECT tags FROM BlogEntry

+--------+-----------------------------------+
| @rid   | tags                              |
+--------+-----------------------------------+
| #10:34 | ['vim', 'vimscript', 'NERD Tree'] |
| #10:35 | ['sed', 'awk', 'bash', 'grep']    |
| #10:36 | ['Emacs', 'Elisp', 'org-mode']    |
+--------+-----------------------------------+

Here we have a series of blog entries on editing text in unix-like environemnts. The query returns the tags for each entry, which is an EMBEDDEDSET of strings. Normally, you would need to add logic at the application layer to only extract portions of this list. But, using brackets, you can do so from within the query.

• Single Extraction: When querying a sequenced proeprty, you can retrieve a specific entry by using an integer in the brackets. For instance, tag[0].

  orientdb> SELECT tags[0] FROM BlogEntry
  
  +--------+-----------+
  | @rid   | tags      |
  +--------+-----------+
  | #10:34 | ['vim']   |
  | #10:35 | ['sed']   |
  | #10:36 | ['Emacs'] |
  +--------+-----------+
  

• Group Extraction: When querying a sequenced property, you can retrieve groups of entries by separating them by a comma. For instance, tag[0,2].

  orientdb> SELECT tags[0,2] FROM BlogEntry
  
  +--------+-----------------------+
  | @rid   | tags                  |
  +--------+-----------------------+
  | #10:34 | ['vim', 'NERD Tree']  |
  | #10:35 | ['sed', 'bash']       |
  | #10:36 | ['Emacs', 'org-mode'] |
  +--------+-----------------------+
  

• Range Extraction: When querying a sequenced property, you can retrieve a range of entries by separating the lower and upper bounds by a hyphen. For instance, tag[1-2].

  orientdb> SELECT tags[1-2] FROM BlogEntry
  
  +--------+----------------------------+
  | @rid   | tags                       |
  +--------+----------------------------+
  | #10:34 | ['vimscript', 'NERD Tree'] |
  | #10:35 | ['awk', 'bash']            |
  | #10:36 | ['Elisp', 'org-mode']      |
  +--------+----------------------------+
  

• Map Extraction: When querying a mapped property, you can retrieve a specific entry from the map by passing the key. For instance, phone['home'].

  orientdb> SELECT FROM BlogEntry 
            WHERE author['group'] LIKE 'Club Linux' 
  

• Conditional Extraction In addition to positional and map values, you can also perform extraction based on conditions. For instance,

  orientdb> SELECT FROM BlogEntry
            WHERE author[group = 'Club Linux']
  

  For more information, see Conditional Extraction.

Using Extraction in SQL

You can use brackets to extract specific parts from the Document field. For instance, consider an address book database that maps phone numbers to types of phone numbers, (such as their home number or mobile).

For instance, say you want to query the address book for the home numbers of users living in Italy:

orientdb> SELECT name, phones FROM Profile
          WHERE phones['home'] LIKE '+39%'

You can chain maps together, such as in cases where a property is a map of a map.

orientdb> SELECT name, contacts FROM Profile
          WHERE contacts[phones][home] LIKE '+39%'

Using Conditional Extraction

In addition to sequential positions and map values, you can also use conditional statements in extraction. For instance,

employees[label = 'Ferrari']

Currently, only the equals condition is supported.

Consider the example of a Graph Database. You can cross a graph using a projection, such as traversing all retrieved nodes with the name "Tom" and viewing their outgoing edges, (here, a collection field called out).

Without extraction, you would do this using dot notation. For instance,

orientdb> SELECT out.in FROM Person WHERE name = 'Tom'

You can filter collections using the equals operator. For instance, you might want to find out how many Toms in the database own Ferraris. You could do this by performing extraction on the out property, so that it matches the incoming edge to the label "Ferrari":

orientdb> SELECT out[in.label = 'Ferrari'] FROM Person
          WHERE name = 'Tom'

You might want a more generic return and only match by class the incoming edges that are cars.

orientdb> SELECT out[in.@class = 'Car'] FROM Person
          WHERE name = 'Tom'

Alternatively, you could use both together:

orientdb> SELECT out[label = 'drives'][in.@class = 'Car']
          FROM Person WHERE name = 'Tom'

Where brackets follow brackets, OrientDB filters the result-set in steps as in a pipeline.

Bear in mind, this does not replace Gremlin support. There's a lot more that you can do with Gremlin than you can through extraction, but extraction is a simpler tool for traversing relationships.

Future directions

In the future you will be able to use the full expression of the OrientDB SQL language inside the square 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