Delete query for Unest

SQL++
1

Hi,

I am able to get data for
Select META(d).id from DeliveryAdapter d UNNEST d.vanTripDetail O order by O.departureTime asc LIMIT 4
data is as below
[
{
“id”: “2639_3684_2017-03-07”
},
{
“id”: “2639_4115_2017-03-09”
},
{
“id”: “2639_4141_2017-03-09”
},
{
“id”: “2639_3684_2017-03-13”
}
]

I want a subquery to delete these 4 rows
i tried with

Delete from DeliveryAdapter r where Meta®.id in (
Select META(d).id from DeliveryAdapter d UNNEST d.vanTripDetail O order by O.departureTime asc LIMIT 4)

but it’s not deleting.

How to get the query to delete

Thanks,
Vinay J

2

If left side of IN clause is values(not object) the right side of the IN clause is subquery the projection of subquery needs to use RAW to generate array of values. Otherwise it generates array of objects.

Delete from DeliveryAdapter r where Meta(r).id in (
Select RAW META(d).id from DeliveryAdapter d UNNEST d.vanTripDetail O order by O.departureTime asc LIMIT 4)

Best way to do this query is To avoid full index scan on outside query

DELTE FROM DeliveryAdapter 
 USE KEYS (Select RAW META(d).id from DeliveryAdapter d UNNEST d.vanTripDetail O order by O.departureTime asc LIMIT 4);