例: JSONデータの更新

ADD句を使用すると、NoSQL表のJSONデータを更新できます。ADD句を使用して、1つ以上の配列要素を既存の配列に追加できます。オプションで、配列に追加する新しい要素の位置を指定することもできます。

例1: JSONデータの既存の配列への単一要素の追加

People表には、現在、次に示すように1つの行があります。

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"
  }
}

配列の最初のほうにあるphones配列に新しい要素を追加します。

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"
  }
}

例2: JSONデータの既存の配列への要素の配列の追加

配列の複数の要素をJSONデータの既存の配列に追加する必要がある場合は、カッコ内に新しい要素の式を追加し、オプションで位置式(ある場合)を追加する必要があります。

次の問合せでは、次のようにエラーがスローされます。

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]

これは、次に示すように、2つの異なるオプションのいずれかを使用して修正できます。

オプション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

オプション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

UPDATE文の結果は、次に示すように検証できます。

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"
  }
}