[SQL] SurrealDB filter nested array with multiple conditions on same element

Here is a sample record for a SurrealDB sandbox database

{
locations: [
{
done: true,
place: 'A',
time: 2
},
{
done: false,
place: 'B',
time: 2
}
]


}

I want to query the database and find the records where place = 'A' and where done = false. There are multiple records in the sandbox where a few match with these conditions. I tried the following query:

SELECT * FROM test WHERE locations.place = "A" AND locations.done = false ORDER BY locations.time DESC


This query did not return any result

I also tried:

SELECT *
FROM test WHERE
"A" in locations[*].place AND false in locations[*].done


However, this returns this sample even though the location where place = 'A'and done = true. This seems to happen because this query simply looks through all the locations and finds one item where place = 'A' and another location where done = false.

The documentation is pretty sparse on this subject. How can I query the database in such a way that I retrieve records where these conditions exist on the same element in the nested array? And also sort by the time field of this specific element?

Continue reading...