dbshell update

Description

The dbshell update command updates JSON documents using OJAI mutations. An OJAI mutation allows you to append, decrement, delete, increment, combine, replace, and update fields in a JSON document.

The following table lists the mutations OJAI supports. See Using OJAI Mutation Syntax for a detailed description of all operations. Each operation in the table links to examples in this topic.

Mutation Operation Description
Append Appends values to binary, string, and array fields
Decrement Decrements field values
Delete Deletes fields
Increment Increments field values
Merge Combines nested documents with existing documents
Put Replaces field values or adds new fields
Set Updates field values or adds new fields

Parameters

update Options Description

*, --t, --table

(Required)

Table path

--id

(Required)

ID of the document to update

NOTE You can specify this parameter only once.

--m, --mutation

(Required)

OJAI document mutation in JSON format

See Using OJAI Mutation Syntax for a description of the syntax.

--c, --where

OJAI condition, in JSON format

The condition must qualify to perform the update. See OJAI Query Condition Syntax for a description of the syntax.

Syntax

update <table path> --id <id> --m <mutation> [ --c <condition> ]
NOTE If the mutation provided as a part of the --m parameter has spaces, then you must enclose it within single quotes.

Sample JSON Document

The dbshell update examples in this topic use the following sample JSON document:

{
  "_id": "id1",
  "a": {
       "b": [{"boolean":false}, {"decimal": 123.456}],
       "c":{
             "d":10,
             "e":"Hello"
           }
       },
  "m":"MapR wins"
}     

Append Operation

This example performs append operations on fields a.b and a.c.e:

update /tbl --id id1 --m {
      "$append":[{"a.b":{"appd":1}},{"a.c.e":" MapR"}]
      }

When you apply this update command to the sample JSON document, the following is the resulting document:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "boolean" : false }, { "decimal" : 123.456 }, { "appd" : 1 } ],
    "c" : {
      "d" : 10,
      "e" : "Hello MapR"
    }
  },
  "m" : "MapR wins"
}

For more details about the $append operation, see OJAI Append Mutations.

Decrement Operation

This example performs a decrement operation:

update /tbl --id id1 --m {
      "$decrement":{"a.c.d":5}
      }

When you apply this update command to the sample JSON document, the following is the resulting document:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "boolean" : false }, { "decimal" : 123.456 } ], 
    "c" : {
      "d" : 5,
      "e" : "Hello"
    }
  },
  "m" : "MapR wins"
}
NOTE To decrement multiple fields, use an an array to specify the fields.

For more details about the $decrement operation, see OJAI Decrement Mutations.

Delete Operation

With the following example, the operation deletes multiple field paths in the document in a single command:

update /tbl --id id1 --m {
      "$delete": ["a.b[1]","a.c.e"]
      }

When you apply this update command to the sample JSON document, the following is the resulting document:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "boolean" : false } ], 
    "c" : {
      "d" : 10
    }
  },
  "m" : "MapR wins"
}

The following example shows that if you need to delete only a single field, do not use the array notation:

update /tbl --id id1 --m {
      "$delete":"a.b[1]"
      }

For more details about the $delete operation, see OJAI Delete Mutations.

Increment Operation

This example performs an increment operation:

update /tbl --id id1 --m {
      "$increment":{"a.c.d":5}
      }

When you apply this update command to the sample JSON document, the following is the resulting document:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "boolean" : false }, { "decimal" : 123.456 } ], 
    "c" : {
      "d" : 15,
      "e" : "Hello"
    }
  },
  "m" : "MapR wins"
}
NOTE To increment multiple fields, use an an array to specify the fields.

For more details about the $insert operation, see OJAI Increment Mutations.

Merge Operation

This example performs a merge operation:

update /tbl --id id1 --m {
      "$merge":{"a.c":{"d":11,"y":"yo"}}
      }

When you apply this update command to the sample JSON document, the following is the resulting document:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "boolean" : false }, { "decimal" : 123.456 } ],
    "c" : {
      "d" : 11,
      "e" : "Hello",
      "y" : "yo"
    }
  },
  "m" : "MapR wins"
}
NOTE $merge does not support the array format for merging two maps at two different field paths in the document.

For example, the following syntax is incorrect:

// WRONG Syntax
update /tbl --id id1 --m {"$merge":["a":{"b":1},{"a":{"d":"MapR"}}]}

The following syntax is correct:

// CORRECT Syntax
update /tbl --id id1 --m {"$merge":{"a":{"b":1,"d":"MapR"}}}

It results in the following document:

{
  "_id" : "id1",
  "a" : {
    "b" : 1,
    "c" : {
      "d" : 10,
      "e" : "Hello"
    },
    "d" : "MapR"
  },
  "m" : "MapR wins"
}

To merge multiple field paths that are non-overlapping, use the syntax described at either Multiple Mutation Operations or Updates Without Explicit Mutation Operation Names.

For more details about the $merge operation, see OJAI Merge Mutations.

Put Operation

This example performs a put operation. Unlike the set operation, the put replaces field values. Like the set operation, you do not need an array representation for a single field.

update /tbl --id id1 --m {
      "$put":[{"a.b":{"boolean":true},{"a.c.d":"eureka"},{"a.x":1}]
      }

When you apply this update command to the sample JSON document, the following is the resulting document:

{
  "_id" : "id1",
  "a" : {
    "b" : { "boolean" : true },
    "c" : {
      "d" : "eureka",
      "e" : "Hello"
    },
    "x" : 1
  },
  "m" : "MapR wins"
}

For more details about the $set operation, see OJAI Put Mutations.

Set Operation

With this example, the command updates the document fields a.b[0].boolean, a.c.d, and a.x. If the field does not exist, the update command creates and sets it. The update fails if the existing field type does not match the new value. If the field exists and is the same type, the value is updated.

update /tbl --id id1 --m {
        "$set":[{"a.b[0].boolean":true},{"a.c.d":11},{"a.x":1}]
        }

When you apply this update command to the sample JSON document, the following is the resulting document:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "boolean" : true }, { "decimal" : 123.456 } ],
    "c" : {
      "d" : 11,
      "e" : "Hello"
    },
    "x" : 1
  },
  "m" : "MapR wins"
}
NOTE If you need to set only a single field, the command looks like the following:
update /tbl --id id1 --m {
          "$set": {"a.b[0].boolean":true}
          }

For more details about the $set operation, see OJAI Set Mutations.

Multiple Mutation Operations

You can combine more than one mutation operation in a single OJAI mutation by specifying each operation separated by a comma.

The following is an example that combines multiple operations:

update /tbl --id id1 --m
        '{
        "$set":{"x":[1,2,3]},
        "$put":{"a.c.e":{"$binary":"AAAADg=="}},
        "$increment":"a.b[1].decimal",
        "$delete":"a.b[0]",
        "$merge":{"newDoc":{"k":"MapR DBShell rocks!!"}},
        "$append":{"m":"!!!"}
        }'

The following is the resulting output:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "decimal" : 124.456 } ],
    "c" : {
      "d" : 10,
      "e" : { "$binary" : "AAAADg==" }
    }
  },
  "m" : "MapR wins!!!",
  "newDoc" : { "k" : "MapR DBShell rocks!!" },
  "x" : [ 1, 2, 3 ]
}

The operations behave in the following manner:

  • The $set operation adds a new array [1,2,3] with field path x into the document.
  • The $put operation replaces the existing string "Hello" with a nested document {"$binary":"AAAADg=="}.
  • The $delete operation deletes the field path a.b[0] from the document.
  • The $merge operation merges a new nested document {"newDoc":{"k":"MapR DBShell rocks!!"}}.
  • The $append operates appends the string "!!!" to the end of the string "MapR wins".
  • The $increment and $delete operate on different elements of the array a.b:
    • The $increment operation increments the value 123.456 in the second element of the array a.b.
    • The $delete operation deletes the field path a.b[0], resulting in a single element array a.b.
Conflicting Operations

When you specify a mutation with field paths that are overlapping, MapR Database detects the conflict, discards the previous conflicting operation, and proceeds with the next operation.

For example, suppose you have the following document:
{"_id":"id1", "a":{"b":{"c":5}}}
The following mutation has two operations with overlapping fields a.b:
{"$delete":"a.b","$set":{"a.b.d":10}}
You may have intended for the mtuation to first delete a.b and then to replace it with a.b.d as follows:
{"_id":"id1", "a":{"b":{"d":"10"}}}
But the actual result is the following:
{"_id":"id1", "a":{"b":{"c":5,"d":"10"}}}

In this case, the set operation on a.b.d causes the delete operation on a.b to be discarded.

NOTE In the earlier example in this section, the $increment and $delete operations are not conflicting because one operates on a.b[1], while the other operates on a.b[0]. On the other hand, the following are conflicting operations:
{"$increment":"a.b[1].decimal","$delete":"a.b"}

Updates Without Explicit Mutation Operation Names

As part of the update command, you can merge a nested document with a document without specifying a mutation operation name. When applying this type of update, the behavior is the same as the merge operation.

For example, suppose you run the following command:

update /tbl --id id1 --m {
      "k":"eureka",
      "a":{"c":{"d":1234}}
      }

If the document with key "id1" exists, the update command merges the nested document with the original document. If the document does not exist, the update creates a new document with the input provided.

Application of the command to the sample document results in the following:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "boolean" : false }, { "decimal" : 123.456 } ],
    "c" : {
      "d" : 1234,
      "e" : "Hello"
    }
  },
  "k" : "eureka",
  "m" : "MapR wins"
}

For the following update command:

update /tbl --id id1 --m {
      "k":"eureka",
      "a":{"c":{"d":null}}
      }

This is the resulting document:

{
  "_id" : "id1",
  "a" : {
    "b" : [ { "boolean" : false }, { "decimal" : 123.456 } ],
    "c" : {
      "d" : null,
      "e" : "Hello"
    }
  },
  "k" : "eureka",
  "m" : "MapR wins"
}
NOTE In this example, field a.c.d remains in the document and is set to null.