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?

I'm assuming you have a table created similarly to:

insert into test [
    {
        name: "Group1",
        locations: [
                {
                done: true,
                place: 'A',
                time: 2
            },
            {
                done: false,
                place: 'B',
                time: 2
            },
        ]
    },
    {
        name: "Group2",
        locations: [
                {
                done: true,
                place: 'C',
                time: 3
            },
            {
                done: false,
                place: 'D',
                time: 7
            },
        ]
    }

] return none;

Your locations are arrays within each of test's records. To operate on these arrays use the array functions within SurrealDB (https://surrealdb.com/docs/surrealql/functions/database/array).

In your case you want to return a test record when any of the locations in its array match on given constraints.

This can be defined two ways - first the older way:

select * from test where array::any(locations, |$l| $l.place = 'A' and $l.done is true);

Or with a more modern syntax:

select * from test where locations.any(|$l| $l.place = 'A' and $l.done is true);

In either case:

• We run through each of the records in test.
• We take each element in locations.
• We assign that element to the $l variable.
• We perform some tests on the parameters of $l.
• If any of those tests are true, the any() function evaluates to the true and the corresponding test record is returned.

Based upon the above test data, both of the select statements above return:

[
    {
        id: test:i51szbjfpf12b9usgu1i,
        locations: [
            {
                done: true,
                place: 'A',
                time: 2
            },
            {
                done: false,
                place: 'B',
                time: 2
            }
        ],
        name: 'Group1'
    }
]