Arangodb AQL连接、合并、嵌入嵌套的三个或更多集合

kiz8lqtg 于 2022-12-09 发布在 Go

关注(0)|答案(2)|浏览(278)

我基于示例Arangodb doc here创建了以下集合，但添加了第三个集合region

Users
{ 
  "name" : { 
    "first" : "John", 
    "last" : "Doe" 
  }, 
  "city" : "cities/2241300989", 
  "_id" : "users/2290649597", 
  "_rev" : "2290649597", 
  "_key" : "2290649597" 
}

Cities
{ 
  "population" : 1000, 
  "name" : "Metropolis", 
  "region" : "regions/2282300990", 
  "_id" : "cities/2241300989", 
  "_rev" : "2241300989", 
  "_key" : "2241300989" 
}

Regions
{  
  "name" : "SomeRegion1", 
  "_id" : "regions/2282300990", 
  "_rev" : "2282300990", 
  "_key" : "2282300990" 
}

我想要一个这样的目标结果

[ 
  { 
    "user" : { 
      "name" : { 
        "first" : "John", 
        "last" : "Doe" 
      }, 
      "_id" : "users/2290649597", 
      "_rev" : "2290649597", 
      "_key" : "2290649597" 
    }, 
    "city" : { 
      "population" : 1000, 
      "name" : "Metropolis",
      "_id" : "cities/2241300989", 
      "_rev" : "2241300989", 
      "_key" : "2241300989",
      "region" : {
          "name" : "SomeRegion1", 
          "_id" : "regions/2282300990", 
          "_rev" : "2282300990", 
          "_key" : "2282300990" 
      }
    } 
  } 
]

Arangodb文档here中的示例仅包含对两个集合的查询

FOR u IN users 
    FOR c IN cities 
        FILTER u.city == c._id  RETURN merge(u, {city: c})

# However I want to have more than two collections e.g.
FOR u IN users 
  FOR c IN cities 
    For r IN regions
      FILTER u.city == c._id and c.region == r._id RETURN merge(????????)

你如何得到上面三个集合的连接结果？如果我想要第四个嵌套的集合会发生什么？

arangodb

来源：https://stackoverflow.com/questions/69100793/arangodb-aql-joining-merging-embedding-nested-three-collections-or-more

2条答案

按热度按时间

oxosxuxt1#

当存储引用另一个集合的文档_id时，可以利用DOCUMENT AQL命令。
因此，您的AQL查询变得简单一些，如下所示：

FOR u IN users
  LET city = DOCUMENT(u.city)
  LET city_with_region = MERGE(city, { region: DOCUMENT(city.region})
  RETURN MERGE(u, { city: city_with_region})

这个查询可以进一步折叠，但我将其保留为这样，以便它更能自我记录。
DOCUMENT最酷的地方在于，可以只返回文档的单个属性，比如LET region_name = DOCUMENT(city.region).name。
我还发现，在大多数情况下，它比执行子查询来定位文档的性能更高。

赞(0）回复(0）举报 2022-12-09

dpiehjr42#

大概是这样的：

FOR u IN users 
  FOR c IN cities 
    For r IN regions
      FILTER u.city == c._id AND c.region == r._id
      RETURN { user: u, city: MERGE(c, {region: r } }

为什么要存储id而不是键来引用城市和地区呢？_id只是一个虚拟字段，它由_key加上集合名称（加上一个斜杠）组成。所以这样做也是一样的（我故意省略了internal _id和_rev字段）：

Users
{ 
  "name" : { 
    "first" : "John", 
    "last" : "Doe" 
  }, 
  "city" : "2241300989", 
  "_key" : "2290649597"
}

Cities
{ 
  "population" : 1000, 
  "name" : "Metropolis", 
  "region" : "2282300990",
  "_key" : "2241300989" 
}

Regions
{  
  "name" : "SomeRegion1",
  "_key" : "2282300990"
}

FOR u IN users 
  FOR c IN cities 
    For r IN regions
      FILTER u.city == c._key AND c.region == r._key
      RETURN { user: u, city: MERGE(c, {region: r } }

赞(0）回复(0）举报 2022-12-09

我来回答

Arangodb AQL连接、合并、嵌入嵌套的三个或更多集合

2条答案

相关问题

热门标签

最新问答