RethinkDB - Updating nested array

Question

I have a survey table that looks like so:

{
  id: Id,
  date: Date,
  clients: [{
    client_id: Id,
    contacts: [{
      contact_id: Id,
      score: Number,
      feedback: String,
      email: String
    }]
  }]
}

I need to updated the score and feedback fields under a specific contact. Currently, I am running the update like this:

function saveScore(obj){
  var dfd = q.defer();
  var survey = surveys.get(obj.survey_id);

  survey 
    .pluck({ clients: 'contacts' })
    .run()
    .then(results => {

      results.clients.forEach((item, outerIndex) => {
        item.contacts.forEach((item, index, array) => {
          if(Number(item.contact_id) === Number(obj.contact_id)) {
            array[index].score = obj.score;
            console.log(outerIndex, index);
          }
        });
      });

      return survey.update(results).run()
    })
    .then(results => dfd.resolve(results))
    .catch(err => dfd.resolve(err));

  return dfd.promise;
};

When I look at the update method, it specifies how to update nested key:value pairs. However, I can't find any examples to update an individual item in an array.

Is there a better and hopefully cleaner way to update items in a nested array?

Jorge Silva · Answer 1 · 2015-05-15T17:54:41.350

You might need to get the array, filter out the desired value in the array and then append it again to the array. Then you can pass the updated array to the update method.

Example

Let's say you have a document with two clients that both have a name and a score and you want to update the score in one of them:

{
  "clients": [
    {
      "name":  "jacob" ,
      "score": 200
    } ,
    {
      "name":  "jorge" ,
      "score": 57
    }
  ] ,
  "id":  "70589f08-284c-495a-b089-005812ec589f"
}

You can get that specific document, run the update command with an annonymous function and then pass in the new, updated array into the clients property.

r.table('jacob').get("70589f08-284c-495a-b089-005812ec589f")
  .update(function (row) {
    return {
      // Get all the clients, expect the one we want to update
      clients: row('clients').filter(function (client) {
        return client('name').ne('jorge')
      })
      // Append a new client, with the update information
      .append({ name: 'jorge', score: 57 })
    };
  });

I do think this is a bit cumbersome and there's probably a nicer, more elegant way of doing this, but this should solve your problem.

Database Schema

Maybe it's worth it to create a contacts table for all your contacts and then do a some sort of join on you data. Then your contacts property in your clients array would look something like:

{
  id: Id,
  date: Date,
  clients: [{
    client_id: Id,
    contact_scores: {
      Id: score(Number)
    },
    contact_feedbacks: {
      Id: feedback(String)
    }
  }]
}

If I understand correctly, this solution does not allow for a single object in the array to be updated, and changes the ordering of the objects in the array? All objects in the array that match `{ name: 'jorge' }` are filtered out, and then the updated `{ name: 'jorge'}` object is appended to the array. — tim-montague, Apr 21 '19 at 12:28

score 4 · Answer 2 · answered Jan 31 '17 at 07:43

4

it works for me

r.table(...).get(...).update({
contacts: r.row('Contacts').changeAt(0,
  r.row('Contacts').nth(0).merge({feedback: "NICE"}))
 })

answered Jan 31 '17 at 07:43

DEO

244
1
4
16

This only works when we know the position of item in array, – SonickSeven Sep 17 '20 at 22:32

score 4 · Answer 3 · answered Jan 29 '18 at 13:10

4

database schema

{
  "clients": [
    {
      "name":  "jacob" ,
      "score": 200
    } ,
    {
      "name":  "jorge" ,
      "score": 57
    }
  ] ,
  "id":  "70589f08-284c-495a-b089-005812ec589f"
}

then you can do like this using map and branch query .

r.db('users').table('participants').get('70589f08-284c-495a-b089-005812ec589f')
  .update({"clients": r.row('clients').map(function(elem){
     return r.branch(
      elem('name').eq("jacob"),
      elem.merge({ "score": 100 }),
      elem)})
    })

answered Jan 29 '18 at 13:10

zabusa

2,102
17
22

Definitely a better solution than the top answer's use of "append" which changes the order of objects in the array. However, this solution also doesn't solve the user's question "I need to update the score and feedback fields under a specific contact", and instead updates all contacts named "jacob". – tim-montague Apr 21 '19 at 12:57
@tfmontague thank you. am starting to learn rethinkdb lack some main concepts. – zabusa Apr 22 '19 at 06:03
@zabusa very nice answer imo. please see my answer that shows how this technique can be abstracted to perform nested updates easily. – Nathan Drake Jul 09 '19 at 17:54

tim-montague · Answer 4 · 2019-07-13T03:59:36.670

ReQL solution

Creating a query to update a JSON array of objects in-place, is a rather complicated process in ReThinkDB (and most query languages). The best (and only) solution in ReQL that I know about, is to use a combination of update,offsetsOf,do,changeAt, and merge functions. This solution will retain the order of objects in the array, and only modify values on objects which match in the offsetsOf methods.

The following code (or something similar) can be used to update an array of objects (i.e. clients) which contain an array of objects (i.e. contracts).

Where '%_databaseName_%', '%_tableName_%', '%_documentUUID_%', %_clientValue_%, and %_contractValue_% must be provided.

r.db('%_databaseName_%').table('%_tableName_%').get('%_documentUUID_%').update(row =>

    row('clients')
      .offsetsOf(clients => client('client_id').eq('%_clientValue_%'))(0)
      .do(clientIndex => ({

        clients: row('clients')(clientIndex)
          .offsetsOf(contacts => contact('contact_id').eq('%_contactValue_%')))(0)
          .do(contactIndex => ({
            contacts: row(clientIndex)
              .changeAt(contractIndex, row(clientIndex)(contractIndex).merge({
                'score': 0,
                'feedback': 'xyz'
              }))
          })
      }))
)

Why go through the trouble of forming this into ReQL?

  survey 
    .pluck({ clients: 'contacts' }).run()
    .then(results => {

      results.clients.forEach((item, outerIndex) => {
        item.contacts.forEach((item, index, array) => {
          if(Number(item.contact_id) === Number(obj.contact_id)) {
            array[index].score = obj.score;
            console.log(outerIndex, index);
          }
        });
      });

      return survey.update(results).run()
    })

While the code provided by Jacob (the user who asked the question here on Stack Overflow - shown above) might look simpler to write, the performance is probably not as good as the ReQL solution.

1) The ReQL solution runs on the query-server (i.e. database side) and therefore the code is optimized during the database write (higher performance). Whereas the code above, does not make full use of the query-server, and makes a read and write request pluck().run() and update().run(), and data is processed on the client-request side (i.e. NodeJs side) after the pluck() query is run (lower performance).

2) The above code requires the query-server to send back all the data to the client-request side (i.e. NodeJs side) and therefore the response payload (internet bandwidth usage / download size) can be several megabytes. Whereas the ReQL solution is processed on the query-server, and therefore the response payload typically just confirms that the write was completed, in other words only a few bytes are sent back to the client-request side. Which is done in a single request.

ReQL is too complicated

However, ReQL (and especially SQL) seem overly complicated when working with JSON, and it seems to me that JSON should be used when working with JSON.

I've also proposed that the ReThinkDB community adopt an alternative to ReQL that uses JSON instead (https://github.com/rethinkdb/rethinkdb/issues/6736).

The solution to updating nested JSON arrays should be as simple as...

r('database.table').update({
  clients: [{
    client_id: 0,
    contacts: [{
      contact_id: 0,
      score: 0,
      feedback: 'xyz',
    }]
  }]
});

Two problems, 1) each time you use `offsetsOf(...)(0)` there's a risk of throwing an error. You can fix that by appropriately using `r.branch` and 2) Your proposed "ReQL is too complicated" JSON query doesn't make sense. Which contact gets updated in which client? contact `0` in client `0`? — Nathan Drake, Jul 09 '19 at 16:32
@NathanDrake - (1) Could you explain the risk of throwing an error, and why `r.branch` solves that issue? I don't quite understand. (2) The intent of the code was simply to convey the idea. However, I've added the `client_id` and `contact_id` fields to make the example more literal. — tim-montague, Jul 10 '19 at 18:13
Well in the event that `offsetsOf(...)` returns an empty result, there will be no `(0)` to return. Ie, `r.expr([1,2,3]).offsetsOf(x => x.gt(3))(0)` results in error, `e: Index out of bounds: 0`. Using `map` mitigates this issue. — Nathan Drake, Jul 11 '19 at 00:25
In the updated proposal for the JSON update, you can add `{ client_id, ... }` and `{ contact_id, ...}` to the patch but there's nothing that tells rethinkdb _which_ record in the array to update. You wish for `client_id` and `contact_id` to be used as some sort of index/key, but rethinkdb has no concept of index/key on arrays - only for _tables_. — Nathan Drake, Jul 11 '19 at 00:30
@NathanDrake - "but rethinkdb has no concept of index/key on arrays - only for tables" - yeah but that's not what I'm suggesting for an array. The proposal is to use the defined JSON structure, and match it against the element (object structure) in the array. So it's not even about ids, it's about how closely the two structures match. — tim-montague, Jul 13 '19 at 04:16
@NathanDrake - "Well in the event that offsetsOf(...) returns an empty result, there will be no (0) to return. [And] results in error, e: Index out of bounds: 0". - So what is the problem? If a key-value to be updated doesn't exist, then an error should be thrown, so that the developer can decide how to handle the error. — tim-montague, Jul 13 '19 at 05:00

score 0 · Answer 5 · answered Jul 09 '19 at 17:53

tfmontague is on the right path but I think his answer can be improved a lot. Because he uses ...(0) there's a possibility for his answer to throw errors.

zabusa also provides a ReQL solution using map and branch but doesn't show the complete nested update. I will expand on this technique.

ReQL expressions are composable so we can isolate complexity and avoid repetition. This keeps the code flat and clean.

First write a simple function mapIf

const mapIf = (rexpr, test, f) =>
  rexpr.map(x => r.branch(test(x), f(x), x));

Now we can write the simplified updateClientContact function

const updateClientContact = (doc, clientId, contactId, patch) =>
  doc.merge
  ( { clients:
        mapIf
        ( doc('clients')
        , c => c('client_id').eq(clientId)
        , c =>
            mapIf
            ( c('contacts')
            , c => c('contact_id').eq(contactId)
            , c =>
                c.merge(patch)
            )
        )
    }
  );

Use it like this

// fetch the document to update
const someDoc =
  r.db(...).table(...).get(...);

// create patch for client id [1] and contact id [12]
const patch =
  updateClientContact(someDoc, 1, 12, { name: 'x', feedback: 'z' });

// apply the patch
someDoc.update(patch);

Here's a concrete example you can run in reql> ...

const testDoc =
  { clients:
      [ { client_id: 1
        , contacts:
            [ { contact_id: 11, name: 'a' }
            , { contact_id: 12, name: 'b' }
            , { contact_id: 13, name: 'c' }
            ]
        }
      , { client_id: 2
        , contacts:
            [ { contact_id: 21, name: 'd' }
            , { contact_id: 22, name: 'e' }
            , { contact_id: 23, name: 'f' }
            ]
        }
      , { client_id: 3
        , contacts:
            [ { contact_id: 31, name: 'g' }
            , { contact_id: 32, name: 'h' }
            , { contact_id: 33, name: 'i' }
            ]
        }
      ]
  };

updateClientContact(r.expr(testDoc), 2, 23, { name: 'x', feedback: 'z' });

The result will be

{ clients:
    [ { client_id: 1
      , contacts:
          [ { contact_id: 11, name: 'a' }
          , { contact_id: 12, name: 'b' }
          , { contact_id: 13, name: 'c' }
          ]
      }
    , { client_id: 2
      , contacts:
          [ { contact_id: 21, name: 'd' }
          , { contact_id: 22, name: 'e' }
          , { contact_id: 23, name: 'x', feedback: 'z' } // <--
          ]
      }
    , { client_id: 3
      , contacts:
          [ { contact_id: 31, name: 'g' }
          , { contact_id: 32, name: 'h' }
          , { contact_id: 33, name: 'i' }
          ]
      }
    ]
}

"Because he uses ...(0) there's a possibility for his answer to throw errors" - Yeah, but why not just handle the error? If the user is trying to update on a key-value pair that doesn't exist, then an error should be thrown. — tim-montague, Jul 13 '19 at 04:43

score 0 · Answer 6 · answered Sep 17 '20 at 23:54

Better late than never

I had your same problem and i could solve it with two ways:

With specific `client_id`

r.db('nameDB').table('nameTable').get('idRegister')
.update({'clients': r.row('clients')
    .map(elem=>{
        return r.branch(
            elem('client_id').eq('your_specific_client_id'),
            elem.merge({
                contacts: elem('contacts').map(elem2=>
                    r.branch(
                        elem2('contact_id').eq('idContact'),
                        elem2.merge({
                            score: 99999,
                            feedback: 'yourString'
                        }),
                        elem2
                    )
                )
            }),
            elem
        )
    })
})

Without specific `client_id`

r.db('nameDB').table('nameTable').get('idRegister')
.update({'clients': r.row('clients')
    .map(elem=>
        elem.merge({
            contacts: elem('contacts').map(elem2=>
                r.branch(
                    elem2('contact_id').eq('idContact'),
                    elem2.merge({
                        score: 99999,
                        feedback: 'yourString'
                    }),
                    elem2
                )
            )
        })
    )
})

I hope that it works for you, even when happened much time ago

RethinkDB - Updating nested array

6 Answers6

ReQL solution

Why go through the trouble of forming this into ReQL?

ReQL is too complicated

Better late than never

With specific `client_id`

Without specific `client_id`

Linked

RethinkDB - Updating nested array

6 Answers6

ReQL solution

Why go through the trouble of forming this into ReQL?

ReQL is too complicated

Better late than never

With specific client_id

Without specific client_id

Linked

With specific `client_id`

Without specific `client_id`