Example: Updating JSON Data

You can use ADD clause to update JSON data in a NoSQL table. You can add one or more array elements to an existing array using the ADD clause. You can also optionally indicate the position of the new elements to be added in the array.

Example 1: Adding a single element to an existing array in JSON data:

The People table has one row currently as shown below:

SELECT * FROM People

{
  "id" : 0,
  "info" : {
    "address" : {
      "city" : "Santa Cruz",
      "phones" : [{
        "areacode" : 831,
        "kind" : "mobile",
        "number" : 5294368
      }, {
        "areacode" : 650,
        "kind" : "mobile",
        "number" : 3789021
      }, {
        "areacode" : 415,
        "kind" : "home",
        "number" : 6096010
      }],
      "state" : "CA"
    },
    "children" : {
      "Anna" : {
        "age" : 11,
        "friends" : ["Anna", "John", "Maria", "Ada", "Aris"],
        "school" : "school_1"
      },
      "Mary" : {
        "age" : 8,
        "friends" : ["Anna", "Mark", "Ada", "Aris"],
        "school" : "school_3"
      },
      "Ron" : {
        "age" : 3,
        "friends" : ["Julie", "Ada", "Aris"]
      }
    },
    "firstName" : "John",
    "income" : 20000,
    "lastName" : "Doe",
    "profession" : "surfing instructor"
  }
}

Add a new element to the phones array at the beginning of the array.

UPDATE People p ADD p.info.address.phones 0 
{"areacode":499, "number":33864368, "kind":"mobile" }
WHERE id = 0

SELECT * FROM People
{

  "id" : 0,
  "info" : {
    "address" : {
      "city" : "Santa Cruz",
      "phones" : [{
        "areacode" : 499,
        "kind" : "mobile",
        "number" : 33864368
      }, {
        "areacode" : 831,
        "kind" : "mobile",
        "number" : 5294368
      }, {
        "areacode" : 650,
        "kind" : "mobile",
        "number" : 3789021
      }, {
        "areacode" : 415,
        "kind" : "home",
        "number" : 6096010
      }],
      "state" : "CA"
    },
    "children" : {
      "Anna" : {
        "age" : 11,
        "friends" : ["Anna", "John", "Maria", "Ada", "Aris"],
        "school" : "school_1"
      },
      "Mary" : {
        "age" : 8,
        "friends" : ["Anna", "Mark", "Ada", "Aris"],
        "school" : "school_3"
      },
      "Ron" : {
        "age" : 3,
        "friends" : ["Julie", "Ada", "Aris"]
      }
    },
    "firstName" : "John",
    "income" : 20000,
    "lastName" : "Doe",
    "profession" : "surfing instructor"
  }
}

Example 2: Adding an array of elements to an existing array in JSON data

When you need to add more than one element of an array to an existing array in JSON data, you need to add the new-elements expressions inside parentheses and optionally add the position expression (if any).

The following query throws an error as shown below:

UPDATE People p
ADD p.info.address.phones
0 { "areacode":5, "number":1, "kind":"mobile" },
  { "areacode":6, "number":2, "kind":"mobile" }
WHERE id = 0;

Error handling command UPDATE People p
ADD p.info.address.phones
0 { "areacode":5, "number":1, "kind":"mobile" },
  { "areacode":6, "number":2, "kind":"mobile" }
WHERE id = 0:
Error: at (5, 12) mismatched input '<EOF>' expecting {WHERE, ','}, at line 5:12
rule stack: [parse, statement, update_statement]

This can be corrected using the one of the two different options as shown below:

Option 1:

UPDATE People p
ADD p.info.address.phones  
([{ "areacode":1, "number":1, "kind":"mobile" },
  { "areacode":2, "number":2, "kind":"mobile" }
 ][])
WHERE id = 0

{
  "NumRowsUpdated" : 1
}
1 row returned

Option 2:

UPDATE People p
ADD p.info.address.phones
0 [{ "areacode":3, "number":1, "kind":"mobile" },
   {"areacode":4, "number":2, "kind":"mobile" }
  ]
WHERE id = 0

{
  "NumRowsUpdated" : 1
}
1 row returned

The result of the UPDATE statement can be verified as shown below.

select * from People;

{
  "id" : 0,
  "info" : {
    "address" : {
      "city" : "Santa Cruz",
      "phones" : [[{
        "areacode" : 3,
        "kind" : "mobile",
        "number" : 1
      }, {
        "areacode" : 4,
        "kind" : "mobile",
        "number" : 2
      }], {
        "areacode" : 499,
        "kind" : "mobile",
        "number" : 33864368
      }, {
        "areacode" : 831,
        "kind" : "mobile",
        "number" : 5294368
      }, {
        "areacode" : 650,
        "kind" : "mobile",
        "number" : 3789021
      }, {
        "areacode" : 415,
        "kind" : "home",
        "number" : 6096010
      }, {
        "areacode" : 1,
        "kind" : "mobile",
        "number" : 1
      }, {
        "areacode" : 2,
        "kind" : "mobile",
        "number" : 2
      }],
      "state" : "CA"
    },
    "children" : {
      "Anna" : {
        "age" : 11,
        "friends" : ["Anna", "John", "Maria", "Ada", "Aris"],
        "school" : "school_1"
      },
      "Mary" : {
        "age" : 8,
        "friends" : ["Anna", "Mark", "Ada", "Aris"],
        "school" : "school_3"
      },
      "Ron" : {
        "age" : 3,
        "friends" : ["Julie", "Ada", "Aris"]
      }
    },
    "firstName" : "John",
    "income" : 20000,
    "lastName" : "Doe",
    "profession" : "surfing instructor"
  }
}