CODEWITHSUNDEEP
selectcollectionsorientdb
monsul
monsul

Reputation: 3

Select element of collection by highest value of specific attribute

I have several documents stored in my database with the following format:

{
    "text": "foo",
    "items": [{
        "num": 1,
        "value": 1.1
    }, {
        "num": 42,
        "value": 3.14
    }]
}

{
    "text": "bar",
    "items": [{
        "num": 3,
        "value": 5.0
    }]
}

I want to retrieve from each document the "text" and the "value" of the item with the highest "num". So in this example my result would be:

{
    "text": "foo",
    "value": 3.14
}

{
    "text": "bar",
    "value": 5.0
}

Is there any way to solve this problem with OrientDB?

Upvotes: 0

Views: 60

Answers (1)

Alessandro Rota
Alessandro Rota

Reputation: 3570

I tried with these records

enter image description here

and I used this query

select rid,items.num as num,items.value as value from (
select @rid,items,$a[0].max as max from test 
let $a=(select max(items.num) as max from $parent.$current)
unwind items
)
where items.num=max

I got

enter image description here

Best regards, Alessandro

Upvotes: 1

Related Questions