rethinkdb - How can I perform a join/merge of a nested attribute across tables? -

i have table (db name libstats, table name flowcells) full of documents, of have structure this:

{         "barcode": "c3w9uacxx",         "id": "0021732f-2c42-4e9a-90fd-c68bb0d998dc",         "libraries": [             {                 "bases": 2431000000,                 "library_id": "sl58263",                 "perc_raw_clusters": 5.5,                 "pf_reads": 24312986,                 "q30": 92.23,                 "qscore": 35.82,                 "reads": 25834646,                 "lane": 1              },...          ] } 

the objects in 'libraries' array have keys shown. need add key library_name.

i have table libraries in database libraries has library_name information. here's example document table:

{    library_id: 'sl123456',    library_name: 'my_library_name' } 

how can use reql accomplish this? i've gotten far:

r.db('libstats').table('flowcells').merge(function(flowcell){   return {'libraries': flowcell('libraries').map(function(library){     return library.merge(        {'library_name': 'foo'}       )   })   } }).limit(1) 

which gives output in structure want, of attempts acquire library_name attribute using getfield, eqjoin(), , merge() have far proved fruitless:

{         "barcode": "c6841anxx",         "id": "007cae10-de3c-44df-9aee-1de9c88c1c21",         "libraries": [             {                 "bases": 194000000,                 "lane": "1",                 "library_id": "sl91807",                 "library_name": "foo",                 "perc_raw_clusters": 0.9,                 "pf_reads": 1942910,                 "q30": 96.55,                 "qscore": 36.06,                 "reads": 2045599             },         ]     } 

naive implementation

you can following:

r.db('libstats').table('flowcells').merge(function(flowcell){   return {'libraries': flowcell('libraries').map(function(library){     return library.merge({        // query `libraries` table       'library_name': r.db('libraries').table('libraries')         // filter out elements         .filter(function (row) {           // return elements `library_id` equal            // `library_id` in `libstats` table           return row('library_id').eq(library('library_id'))         })         // return `library_name` first element         (0)('library_name')     })   })   } }) 

keep in mind can use secondary index , make bit simpler , more performant.

better solution

if have lot of documents (10k+), you'll want create index on library_id , use following query:

r.table('libstats').merge(function(flowcell){   return {'libraries': flowcell('libraries').map(function(library){     return library.merge({        // query `libraries` table       'library_name': r.table('libraries')         // filter out elements       .getall(library('library_id'), { index: 'library_id' })(0)('library_name')     })   })   } })