例: JSONデータの更新
JSONデータは、JSON MERGE句またはADD句を使用して更新できます。
JSON MERGE句
JSON MERGE句を使用すると、JSONドキュメントに対する一連の変更を行うことができます。次の項の例では、CREATE TABLEトピックのエアライン手荷物追跡アプリケーションおよびストリーミング・メディア・サービス・アプリケーションについて考えてみます。
例7-33 既存のフィールドの更新およびJSONドキュメントへの新しいフィールドの追加
UPDATE BaggageInfo b
JSON MERGE b.bagInfo[0] WITH PATCH {"tagNum" : "18657806255240", "routing" : "MIA/LAX", "lastSeenStation" : "LAX", "bagStatus" : "delivered"}
WHERE ticketNo = 1762344493810 RETURNING b.bagInfo[0].tagNum, b.bagInfo[0].routing, b.bagInfo[0].lastSeenStation, b.bagInfo[0].bagStatus説明: 前述の問合せで、航空会社手荷物追跡アプリケーションの乗客のレコードを更新して、いくつかの不注意なエラーを修正しました。JSON MERGE句は、パッチの内容を現在の内容と比較し、bagInfo JSON配列の最初の要素のtagNum、routingおよびlastSeenStationフィールドを更新します。bagStatusフィールドは現在、bagInfo配列の最初の要素の一部ではないため、JSON MERGEパッチはそれをドキュメントに挿入します。
更新されたフィールドのみを表示するには、RETURNING句を指定してUPDATE文を使用します。
{
"tagNum" : "18657806255240",
"routing" : "MIA/LAX",
"lastSeenStation" : "LAX",
"bagStatus" : "delivered"
}ノート:
ここで、bagInfoはJSONドキュメントの配列で、乗客ごとの受託手荷物を表します。更新する配列要素を指定しない場合、JSON MERGE句によって、bagInfo配列全体がパッチの内容に置き換えられます。1人の乗客に複数の受託手荷物がある場合は、次のように同じUPDATE文でJSON MERGE句を繰り返し使用できます:UPDATE BaggageInfo b
JSON MERGE b.bagInfo[0] WITH PATCH {"tagNum" : "18657806255111", "routing" : "MIA/LAX", "lastSeenStation" : "LAX", "bagStatus" : "delivered"},
JSON MERGE b.bagInfo[1] WITH PATCH {"tagNum" : "18657806255112", "routing" : "MIA/LAX", "lastSeenStation" : "LAX", "bagStatus" : "delivered"}
WHERE ticketNo = 1762320369957例7-34 パッチにJSONパスを使用した新しいフィールドの追加
航空会社の手荷物追跡アプリケーションの次の乗客のデータについて考えてみます。ネストされたflightLegsドキュメントには、フライト番号は含まれていません。
{"ticketNo":1882344493810,"fullName":"Joan Smith","gender":"F","contactPhone":"886-324-1064","confNo":"LE6F4Z","bagInfo":[{"flightLegs":{"estimatedArrival":"2019-02-01T11:00:00Z","flightDate":"2019-02-01T06:00:00Z","fltRouteDest":"LAX","fltRouteSrc":"MIA"},"id":"79039899165297","routing":"MIA/LAX","tagNum":"17657806255240"}]}次のように、flightNoフィールドをflightLegsドキュメントに含めるパッチを適用できます:
UPDATE BaggageInfo b
JSON MERGE b.bagInfo[0] WITH PATCH {"flightLegs": {"flightNo":"BM107"}} WHERE ticketNo = 1882344493810 RETURNING *説明: ここで、パッチ式にflightNoフィールドのJSONパスを指定します。JSON MERGE句は、JSONパスを評価し、bagInfo配列の最初の要素のネストされたflightLegsドキュメントにflightNoフィールドを挿入します。
出力:
{"ticketNo":1882344493810,"fullName":"Joan Smith","gender":"F","contactPhone":"886-324-1064","confNo":"LE6F4Z","bagInfo":[{"flightLegs":{"estimatedArrival":"2019-02-01T11:00:00Z","flightDate":"2019-02-01T06:00:00Z","flightNo":"BM107","fltRouteDest":"LAX","fltRouteSrc":"MIA"},"id":"79039899165297","routing":"MIA/LAX","tagNum":"17657806255240"}]}ノート:
ネストされたflightLegsドキュメントが存在しない場合、JSON MERGEパッチによって作成されます。
例7-35 JSONドキュメントからのフィールドの削除およびネストされたドキュメントの挿入
UPDATE stream_acct s
JSON MERGE s.acct_data WITH PATCH {"country" : NULL, "recommended" : {"showName1" : "laugh it", "showName2": "Mystery solved", "showName3": "bakesnCooks"}}
WHERE acct_id = 1 RETURNING *説明: 前述の問合せでは、JSON MERGE句を使用して、ストリーミング・メディア・サービス・アプリケーションのサブスクライバ・レコードを更新しています。パッチ式で、countryおよびrecommendedフィールドを指定します。JSON MERGEパッチは、新しいネストされたrecommended JSONドキュメントをacct_data JSONドキュメントに挿入します。NULL値を指定して、ドキュメントからcountryフィールドを削除することを示します。
RETURNING句は、更新された行全体をフェッチします。
出力:
{
"acct_id" : 1,
"profile_name" : "AP",
"account_expiry" : "2023-10-18T00:00:00.000000000Z",
"acct_data" : {
"contentStreamed" : [{
"genres" : ["action", "crime", "spanish"],
"numSeasons" : 4,
"seriesInfo" : [{
"episodes" : [{ .... }],
"showId" : 26,
"showName" : "At the Ranch",
"showtype" : "tvseries"
}, {
"genres" : ["comedy", "french"],
"numSeasons" : 2,
"seriesInfo" : [{
"episodes" : [{ .... }],
"showId" : 15,
"showName" : "Bienvenu",
"showtype" : "tvseries"
}],
"firstName" : "Adam",
"lastName" : "Phillips",
"recommended" : {
"showName1" : "laugh it",
"showName2" : "Mystery solved",
"showName3" : "bakesnCooks"
}
}
}例7-36 ドキュメントのフィールドの更新/追加
ストリーミング・メディア・サービス・アプリケーションからの次のサブスクライバ・レコードについて考えてみましょう。
{"acct_id":1,"profile_name":"AP","account_expiry":"2023-10-18T00:00:00.000000000Z","acct_data":{"contentStreamed":[{"genres":["action","crime","spanish"],"numSeasons":4,"showId":26,"showName":"At the Ranch","showtype":"tvseries"},{"genres":["comedy","french"],"numSeasons":2,"showId":15,"showName":"Bienvenu","showtype":"tvseries"}],"firstName":"Adam","lastName":"Phillips"}}
{"acct_id":2,"profile_name":"AW","account_expiry":"2025-12-18T00:00:00.000000000Z","acct_data":{"contentStreamed":{"genres":["comedy","french"],"numSeasons":2,"showId":15,"showName":"Bienvenu","showtype":"tvseries"},"country":"France","firstName":"Adelaide","lastName":"Willard"}}
例4a: JSON MERGE句を使用して、前述のレコードのacct_data JSONドキュメントのcountryフィールドを更新します。
UPDATE stream_acct s
JSON MERGE s.acct_data WITH PATCH { "country" : "USA"} WHERE acct_id = 1 RETURNING s.acct_id, s.acct_data.country
UPDATE stream_acct s
JSON MERGE s.acct_data WITH PATCH { "country" : "Italy"} WHERE acct_id = 2 RETURNING s.acct_id, s.acct_data.country説明: acct_id 1に対する最初のUPDATE文では、元のドキュメントにcountryフィールドが存在しないため、JSON MERGE句はcountryフィールドをacct_data JSONドキュメントに挿入します。2番目のUPDATE文では、JSON MERGE句によって、サブスクライバacct_id 2のcountryフィールド値がFranceからItalyに更新されます。
出力:
{"acct_id":1,"country":"USA"}
1 row returned
{"acct_id":2,"country":"Italy"}
1 row returned例4b: パス式を使用してcountryフィールドを更新します。
ストリーミング・メディア・サービス・アプリケーションの元のサブスクライバ・レコードについて考えてみましょう。
acct_id 2のレコードのパス式を使用して、countryフィールドを更新します。
UPDATE stream_acct s
JSON MERGE s.acct_data.country WITH PATCH "Switzerland" where acct_id = 2
{"NumRowsUpdated":1}説明: UPDATE操作が成功し、countryフィールド値が指定された値に置き換えられました。次に、acct_id 1のサブスクライバ・レコードを更新します。
UPDATE stream_acct s
JSON MERGE s.acct_data.country WITH PATCH "USA" where acct_id = 1
{"NumRowsUpdated":0}例4aのcountryフィールドが正常に挿入されたacct_id 1のUPDATE操作とは対照的に、ここでは何も処理を行いません。パス式を使用して直接アクセスされるフィールドがターゲットJSONドキュメントに存在しない場合、結果のJSON MERGE操作では何も処理を行いません。
例7-37 JSONのNULLフィールドの更新
ストリーミング・メディア・サービス・アプリケーションからの次のサブスクライバ・レコードについて考えてみましょう。
{"acct_id":1,"profile_name":"AP","account_expiry":"2023-10-18T00:00:00.000000000Z","acct_data":null} ここで、acct_dataはNULL値を持つJSONフィールドです。JSON MERGE句を使用してフィールドを更新します。
JSON MERGE s.acct_data WITH PATCH {"contentStreamed":{"genres":["comedy","french"],"numSeasons":2,"showId":15,"showName":"Bienvenu","showtype":"tvseries"},"country":NULL,"firstName":"Adam","lastName":"null"} WHERE acct_id = 1 RETURNING *説明: JSON MERGEパッチは、acct_dataドキュメントのNULL値を、パッチ式から指定されたJSON値に置き換えます。countryフィールドにJSONのNULL値を指定しています。JSON MERGEパッチは、このフィールドをacct_dataドキュメントに挿入しません。ただし、文字列値nullのlastNameフィールドは正常に挿入されます。
出力:
{"acct_id":1,"profile_name":"AP","account_expiry":"2023-10-18T00:00:00.000000000Z","acct_data":{"contentStreamed":{"genres":["comedy","french"],"numSeasons":2,"showId":15,"showName":"Bienvenu","showtype":"tvseries"},"firstName":"Adam","lastName":"null"}}ADD句
ADD句を使用すると、JSONドキュメントのフィールドを更新できます。ADD句を使用して、1つ以上の配列要素を既存の配列に追加できます。オプションで、配列に追加する新しい要素の位置を指定することもできます。
例7-38 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 = 0SELECT * 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"
}
}例7-39 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つの異なるオプションのいずれかを使用して修正できます。
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 returnedUPDATE 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 returnedselect * 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"
}
}