Queries involving Embedded Maps

[StevenTomer]

Using a schema with class person:
class person
property 'name', type string
property 'phone', type embeddedmap, link-type string

I add the following records:

person@name:"Jack",phone:
{"home":"201-555-1000","cell":"201-555-1001","work":"201-555-1002"}
person@name:"Jill",phone:
{"home":"201-556-1000","cell":"201-556-1001","work":"201-555-1002"}
person@name:"Mary",phone:
{"home":"201-556-1000","cell":"201-557-1001","work":"201-555-1002"}

I'd like to do a few types of select, and am unsure of the correct
syntax.

1) A select based on lookups in the map
select from person where phone["home"] = "201-556-1000"

I've tried select from person where phone containsvalue ( home =
"201-556-1000" ) , but I get:
Error: Error on loading record #5:0. Cause:
java.lang.ClassCastException: java.lang.String cannot be cast to
com.orientechnologies.orient.core.record.ORecordSchemaAware
Error: java.lang.String cannot be cast to
com.orientechnologies.orient.core.record.ORecordSchemaAware

2) A select using a projection inside the map
select phone["home"] from person

The only example I find like this that works is using an embeddedmap
of embedded, but
even in that example, the map key is not used for the select.

Thanks,

Steve

[Luca Garulli]

On 11 August 2011 20:57, StevenTomer <steven...@gmail.com> wrote:

Using a schema with class person:
class person
  property 'name', type string
  property 'phone', type embeddedmap, link-type string

I add the following records:

person@name:"Jack",phone:
{"home":"201-555-1000","cell":"201-555-1001","work":"201-555-1002"}
person@name:"Jill",phone:
{"home":"201-556-1000","cell":"201-556-1001","work":"201-555-1002"}
person@name:"Mary",phone:
{"home":"201-556-1000","cell":"201-557-1001","work":"201-555-1002"}

I'd like to do a few types of select, and am unsure of the correct
syntax.

1) A select based on lookups in the map
select from person where phone["home"] = "201-556-1000"

Hi,

I'm sorry but you've found a case where the SQL can't help. There is an open issue for this planned after 1.0:

http://code.google.com/p/orient/issues/detail?id=143

 

Thanks,

Steve

Lvc@

[Luca Garulli]

Hi Steve,

that issue has been fixed in SVN trunk.

You can use this syntax:

select from person where phone["home"] = "201-556-1000"

or this one:

select from person where phone[home] = "201-556-1000"

If you've an embedded map of map/document/list you can get item in chain:

select from person where phone["home"][first] = "201-556-1000"

Lvc@

[StevenTomer]

Brilliant! Can you also do it in the projection, e.g.

select phone["home"] from person

?

Thanks,

Steve

On Aug 12, 8:31 am, Luca Garulli <l.garu...@gmail.com> wrote:
> Hi Steve,
> that issue has been fixed in SVN trunk.
>
> You can use this syntax:
>
> select from person where phone["home"] = "201-556-1000"
>
> or this one:
>
> select from person where phone[home] = "201-556-1000"
>
> If you've an embedded map of map/document/list you can get item in chain:
>
> select from person where phone["home"][first] = "201-556-1000"
>
> Lvc@
>

> On 12 August 2011 00:38, Luca Garulli <l.garu...@gmail.com> wrote:

[Luca Garulli]

Yes,

I've not tested it (the new test case acts only on the WHERE condition) but the implementation is at ODocument level. You can use it also via Java API:

ODocument doc = ...

String phone = doc.field( "contacts[phone]" );

Lvc@

[StevenTomer]

I tried the projection, but it's returning null.

select name, phone[home] from person

and I get:

---+---------+--------------------+--------------------
#| RID |name |phone["home"]
---+---------+--------------------+--------------------
0| |Mary |null
1| |Jill |null
2| |Jack |null
---+---------+--------------------+--------------------

Steve

On Aug 12, 8:49 am, Luca Garulli <l.garu...@gmail.com> wrote:
> Yes,
> I've not tested it (the new test case acts only on the WHERE condition) but
> the implementation is at ODocument level. You can use it also via Java API:
>
> ODocument doc = ...
>
> String phone = doc.field( "contacts[phone]" );
>
> Lvc@
>

[Luca Garulli]

Hi,

I've just tested this query and works (I've added a new test case for this):

select customReferences[second]['name'] as value from Profile where customReferences[second]['name'] is not null

Can you retry with latest SVN release?

Lvc@

[StevenTomer]

With revision 3663, it works if the projection is named using 'AS'.
Without it, I get nulls. See below:

> select name, phone['cell'] as value from person

---+---------+--------------------+--------------------
#| RID |name |value
---+---------+--------------------+--------------------
0| |Mary |201-557-1001
1| |Jill |201-556-1001
2| |Jack |201-555-1001
---+---------+--------------------+--------------------

3 item(s) found. Query executed in 0.006 sec(s).

> select name, phone['cell'] from person

---+---------+--------------------+--------------------
#| RID |name |phone['cell']

---+---------+--------------------+--------------------
0| |Mary |null
1| |Jill |null
2| |Jack |null
---+---------+--------------------+--------------------

3 item(s) found. Query executed in 0.006 sec(s).

On Aug 12, 12:28 pm, Luca Garulli <l.garu...@gmail.com> wrote:
> Hi,
> I've just tested this query and works (I've added a new test case for this):
>
> select customReferences[second]['name'] as value from Profile where
> customReferences[second]['name'] is not null
>
> Can you retry with latest SVN release?
>
> Lvc@
>

[Luca Garulli]

Hi, 

via API works well, probably it's a console problem. Please, could you open an issue for this?

Lvc@