Query Select: map and embedded value

[Thibault]

hi,

I've got something like that:

{ "schema":
{ "id": 2, "name": "Car" },
"result": [{
"@rid": "5:4", "@class": "Car",
"id": "e514c819-4c23-4e56-8352-f64762abec38",
"users": {
"id1": {
"@class": "User",
"id": "id1",
"token": "token1"
},
"id2": {
"@class": "User",
"id": "id2",
"token": "token2"
},
"id3": {
"@class": "User",
"id": "id3",
"token": "token3"
}
}
}]
}

I would like to load the car from the token value.

I can't use CONTAINSKEY because the key is the id and not the token,
so I trued CONTAINSVALUE like that:
SELECT FROM Event WHERE users CONTAINSVALUE (token = "token2")
but without success

I can't switch id and token (i.e, the key = the token). But if this
schema can't manage my use case I can change the map to a list.

[Luca Garulli]

Hi,

you're right. Your use case (quite common) is not covered by current SQL operators. We need a CONTAINSKEY and CONTAINSVALUE that match complex conditions like CONTAINS does but against keys and values of maps.

From the other side CONTAINS now accepts a condition between () to match against the collection items. But today if you've a collection of string you can't do:

SELECT FROM Profile WHERE tags CONTAINS 'Luca'

but you have to do:

SELECT FROM Profile WHERE tags IN 'Luca'

that it could be fine, but in many case the user expects that CONTAINS means "contains" and if doesn't contain sub-records it should go like the IN operator...

Could you open an issue for this?

In the meanwhile I suggest you to use a List and CONTAINS operator.

Lvc@

[Thibault]

Thx for your response.

I will open a new issue.

[Thibault]

Issue created: http://code.google.com/p/orient/issues/detail?id=292

[Luca Garulli]

Fixed in SVN revision 2615: http://code.google.com/p/orient/source/detail?r=2615

Lvc@

[Alex Cruise]

On Wed, Apr 6, 2011 at 6:52 AM, Luca Garulli <l.ga...@gmail.com> wrote:

you're right. Your use case (quite common) is not covered by current SQL operators. We need a CONTAINSKEY and CONTAINSVALUE that match complex conditions like CONTAINS does but against keys and values of maps.

Just a humble suggestion, when you find yourself thinking about adding language support for map-like data structures, why not consider treating them as a collection of Tuple2's (or a nested table-like structure) instead?

I used to use a 4GL with multivalued fields, and they had additional keywords ANY and ALL to use when filtering a multivalued field, indicating whether the predicate must return true for at least one of the values in the field (the default), or all of them.

Thanks!

-0xe1a

[Luca Garulli]

Hi Alex,

OrientDB already supports ANY() and ALL() field as keywords. Furthermore there are CONTAINS and CONTAINSALL operator for both use cases. But this is not supported for CONTAINSKEY and CONTAINSVALUE that returns TRUE if just one item matches the condition.

Probably I could support a syntax like:

select from Profile where customReferences.keys() CONTAINS 'first'

select from Profile where customReferences.values() CONTAINS ( name like 'Ja%')

select from Profile where tags CONTAINS 'smart'

and

select from Profile where customReferences.keys() CONTAINSALL 'first'

select from Profile where customReferences.values() CONTAINSALL ( name like 'Ja%')

select from Profile where tags CONTAINSALL 'smart'

In this way the operator is always CONTAINS that work on the tuple (the records) and the discriminator is the field where to apply the condition: on maps OrientDB could use .keys() and .values() to satisfy both use cases.

Thoughts?

Lvc@

[Luca Garulli]

It was too easy: already implemented and tested in SVN revision: 2616.

You can use both.

Lvc@