RethinkDB eqJoin + MAP

Question

i have this problem with RethinkDB.

I have 3 documents:

Person (the account field is an secundary id):

{
    "account":  "3580654" , 
    "id":  "356ee84a-8eb5-4b9e-8af5-1cf1ebfe556d" ,
    "name":  "Jesus",
    "lastname":  "Omilon"
}

Location (the account field is an secundary id):

{
    "account":  "3580654" , 
    "id":  "53d02b06-8fe2-4d48-a8ab-ffd2ff519a32" ,
    "ip":  "127.0.0.1",
    "state":  "21"
}

Phones (the account field is an secundary id):

{
    "account":  "3580654" , 
    "id":  "692e7e5f-85f2-444f-b6de-a2b1136b80fa" ,
    "phone":  "555-444.44.44",
    "state":  "21"
},
{
    "account":  "3580654" , 
    "id":  "079579aa-ece9-408f-9d00-889a1cb56fc4" ,
    "phone":  "555-432.33.33",
    "state":  "21"
}

so i want to join the query to have something like this:

{
    "account": "3580654",
    "id": "356ee84a-8eb5-4b9e-8af5-1cf1ebfe556d",
    "name": "Jesus",
    "lastname": "Omilon",
    "state": 21,
    "ip": "127.0.0.1",
    "phones": ["555-444.44.44", "555-432.33.33"]
}

Can anyone helpme to do this? i try with eqJoin and map, but without success.

Thanks for your help.

now i have another problem, now i have to order that by Person with phone or exclude records without phones. — mijailr, Oct 23 '15 at 22:06

score 2 · Accepted Answer · answered Oct 23 '15 at 00:10

Thank you for very detail sample data. That helps answer it easier.

Since person and location can just be merged to create final location. We can use eqJoin to join data.

The phone is an array, so we can run a map from above result, inside the map function, we query extra data of phone table and merge with the main document.

The end query looks like this:

r.table('Person').eqJoin('account', r.table('Location'), {index: 'account'}).zip()
  .map(function(doc) {
    return doc.merge({
      'phone': r.table('Phones').getAll(doc('account'), {index: 'account'})('phone').coerceTo('ARRAY')
    })
  })

It produces result:

{
    "account": "3580654",
    "id": "53d02b06-8fe2-4d48-a8ab-ffd2ff519a32",
    "ip": "127.0.0.1",
    "lastname": "Omilon",
    "name": "Jesus",
    "phone": [
        "555-444.44.44",
        "555-432.33.33"
    ],
    "state": "21"
}

Note that here we have a conflict of id field of Person and Location when we call zip(). So you may tweak query a bit to retain the id field that you like

very glad it helps. Thanks for marking answer and upvote :) – kureikain Oct 23 '15 at 19:59 — kureikain, Oct 23 '15 at 19:59

RethinkDB eqJoin + MAP

1 Answers1