CODEWITHSUNDEEP
arangodb
Hoodox
Hoodox

Reputation: 13

Arangodb AQL Joining, merging, embedding nested three collections or more

I have the following collections, based on the example Arangodb doc here but have added a third collection called 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" 
}

I want to have a target result like this

[ 
  { 
    "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" 
      }
    } 
  } 
]

The example in the Arangodb doc here only has queries for two collections

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(????????)

How would you get the result with three collections joined as above? What happens if I want a forth nested one?

Upvotes: 1

Views: 930

Answers (2)

David Thomas
David Thomas

Reputation: 2349

When you store a document _id that references another collection, then you can leverage the DOCUMENT AQL command.

So your AQL query becomes a bit simpler, like this:

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})

This query could be collapsed even more, but I left it like this so it's more self documenting.

What is cool about DOCUMENT is you can return only a single attribute of a document, such as LET region_name = DOCUMENT(city.region).name.

I've also found that in most cases it's more performant that doing a subquery to locate the document.

Upvotes: 1

mpoeter
mpoeter

Reputation: 2949

Probably something like this:

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 } }

Is there a particular reason why you store ids instead of keys to refer to cities and regions? The _id is just a virtual field that consists of the _key prefixed by the collection name (plus a slash). So this would work just as well (I intentionally omit the internal _id and _rev fields):

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 } }

Upvotes: 1

Related Questions