SurrealDB嵌套数组同一元素多条件查询及排序问题

问题场景 #

现有SurrealDB中的示例记录：

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

需求：查询locations数组中同一元素同时满足place='A'且done=false的记录，并按该匹配元素的time字段排序。

此前尝试的两种查询均未达到预期：

1. 无返回结果的查询：

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

2. 误匹配不同元素的查询：

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

该查询错误返回了上述示例记录，原因是它匹配了数组中不同元素的条件——place='A'的元素done为true，done=false的元素place为B，并不满足同一元素同时符合两个条件的要求。

正确查询方案 #

要实现数组内同一元素的多条件匹配，需利用SurrealDB的array::filter函数遍历数组，精准筛选出同时满足所有条件的元素，再基于过滤结果完成记录筛选与排序。

1. 筛选符合条件的记录 #

SELECT * FROM test
WHERE array::len(array::filter(locations, $item => $item.place = "A" AND $item.done = false)) > 0

该查询通过array::filter遍历locations数组，仅保留同时满足place='A'和done=false的元素；再通过array::len判断过滤后的数组长度是否大于0，以此确认记录中存在符合要求的元素。

2. 按匹配元素的time字段排序 #

若需按匹配元素的time字段排序，可先提取匹配元素的time值，再基于该值排序：

SELECT *, array::filter(locations, $item => $item.place = "A" AND $item.done = false)[0].time AS match_time
FROM test
WHERE array::len(array::filter(locations, $item => $item.place = "A" AND $item.done = false)) > 0
ORDER BY match_time DESC

这里通过array::filter获取匹配元素数组，取第一个元素的time作为match_time字段，再以此字段排序。若存在多个匹配元素，可使用array::max取最大的time值排序：

SELECT *, array::max(array::filter(locations, $item => $item.place = "A" AND $item.done = false).time) AS max_match_time
FROM test
WHERE array::len(array::filter(locations, $item => $item.place = "A" AND $item.done = false)) > 0
ORDER BY max_match_time DESC

内容的提问来源于stack exchange，提问作者user3767693