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?