rethinkdb - create new field containing array based on field in parent

Question

Using a public data set I ran this query on a table:

r.table("contacts") .filter({"Type": "Agent","ContactDescription" : "CONDO"}) .hasFields("CorporationName") .group("CorporationName") .ungroup() .merge(function(row){ return {count: row('reduction').count()}; }) .orderBy(r.desc('count'))

to produce this output:

{
    "count": 167,
    "group": "THE ANDREWS ORGANIZATION",
    "reduction": [
        {
            "BusinessApartment": "12",
            "BusinessCity": "NEW YORK",
            "BusinessHouseNumber": "666",
            "BusinessState": "NY",
            "BusinessStreetName": "BROADWAY",
            "BusinessZip": "10012",
            "ContactDescription": "CONDO",
            "CorporationName": "THE ANDREWS ORGANIZATION",
            "FirstName": "EUGENE",
            "LastName": "ANDREWS",
            "RegistrationContactID": "37946804",
            "RegistrationID": "379468",
            "Type": "Agent",
            "id": "09a89cc3-67ee-4a95-b8db-cd118cb5e40a"
        },
        {
            "BusinessApartment": "12",
            "BusinessCity": "NEW YORK",
            "BusinessHouseNumber": "666",
            "BusinessState": "NY",
            "BusinessStreetName": "BROADWAY",
            "BusinessZip": "10012",
            "ContactDescription": "CONDO",
            "CorporationName": "THE ANDREWS ORGANIZATION",
            "FirstName": "EUGENE",
            "LastName": "ANDREWS",
            "RegistrationContactID": "14378404",
            "RegistrationID": "143784",
            "Type": "Agent",
            "id": "23fbb5ca-b66c-4784-893c-e60752f9386d"
        },

How can I modify the query so that a new field (array) is created in each object called "siblings" based off this query:

r.table("contacts").getAll(reduction.RegistrationID, {index: "RegistrationID"})

Jorge Silva · Accepted Answer · 2015-08-04T16:46:05.553

1

You can do the following:

r.table("contacts")
 .filter({"Type": "Agent","ContactDescription" : "CONDO"})
 .hasFields("CorporationName") 
 .group("CorporationName") 
 .ungroup() 
 .merge(function(row){ return {count: row('reduction').count()}; })  
 // Start Query
 // Map over every single row/result and merge a `reduction` property
 .merge(function (row) {
    return {
      // Map over every object in the reduction
      'reduction': row('reduction').map(function (obj) {
        // Add a `siblings` property to the object
        return obj.merge({
          // Execute your query and coerce it to an array
          'siblings': r.table("contacts")
            .getAll(obj('RegistrationID'), {index: "RegistrationID"}).coerceTo('array')
        })
      })
    }
  })
 // End
 .orderBy(r.desc('count'))

edited Aug 04 '15 at 16:46

answered Aug 02 '15 at 20:34

Jorge Silva

4,574
1
23
42

This query works in generating siblings, but it does not order by count, because it no longer generates a count field, probably because there is also no reduction field. Any idea why and how to get around this? – Barry G Aug 04 '15 at 12:09
Should be fixed. Just changed it from `map` to `merge` and had it return an object with a `reduction` property! – Jorge Silva Aug 04 '15 at 16:46

rethinkdb - create new field containing array based on field in parent

1 Answers1