Background
A general direction that we see in NoSQL and relational databases is the adoption of the document model. While each document-oriented database implementation differs on the details of this definition, in general, they all assume documents encapsulate and encode data (or information) in some standard format or encoding such as XML, YAML, JSON, and BSON .
JavaScript Object Notation (JSON) has become the accepted medium for interchange between numerous Internet services and for unstructured data. The JSON-style document model enables customers to build services that are schema-less.
Sure, you could always store JSON as an opaque JSON blob in Database, but that meant that you could not easily work with JSON data at the database level. Starting with Oracle NoSQL Database 4.2.10 we added support for a native JSON data type defined by RFC 7159 that enables efficient access to data in JSON documents. The JSON data type provides following benefits over storing JSON-format strings in a string column :
*
Automatic JSON Document Validation : Only valid JSON documents can be stored in a JSON column, so you get automatic validation of your data. Invalid documents produce an error while doing the “put” or insert operation in Oracle NoSQL Database.
*
Efficient Access : JSON documents stored in JSON columns are converted to an internal binary (optimized) format that allows quick read access to document elements. Let’s say you want to find a particular name/value pair from a JSON document and If you were to store JSON documents as blob, your best option would be to read the JSON data in memory, run it through a JSON parser, which meant that you have to scan the JSON document and convert at the application level – needless to say it won’t be performant. Conversely, with native JSON data type, the database parses and stores JSON internally in native data format and querying JSON data is no different than querying data in other columns.
*
Performance : Improve your query performance by creating indexes on a specific field within the JSON column value.
We continue to enhance the Oracle NoSQL Database support for JSON datatypes. This includes recent enhancements:
*
Indexes into JSON column. An index is a JSON index if it indexes at least one field that is contained inside JSON data. We support both simple type JSON indexes and multi-key types (which index arrays or map) JSON indexes. This allowed developers access to the nested attributes embedded deep within a JSON document.
*
Queries (SQL for NoSQL) invovlving JSON data types.
*
Update JSON documents using SQL Update Statement. An Update statement can be used to update a row in a table that contains JSON column. This also allow users to perform partial updates or updating a subset of fields in the JSON column.
While each of above point deserve a separate blog post, in this post, we are going to focus on the last point about performing an update on the JSON document using the SQL Update statement.
Updating JSON Documents
While some systems don’t recommend manipulating JSON documents, Oracle NoSQL Database with SQL Update statement has made is possible for developers to modify or partially update (subset of fields) JSON documents.
Let’s take a real world example, where you want to model some User profile data (personal data associated with specific users) in Oracle NoSQL Database. User profile makes an interesting case for JSON data type, because a user profile data can have varying sets of attributes – users have different interest and expense patterns, some users have more than one work address, some users have more than one type of phone (work, home etc), some are like to specify firstInitial}{lastname} and others want {firstname}.{lastname} etc.
Thus, a user profile data doesn’t really conform to the rigid schema (schema, data-types etc) of the traditional system and there is a need for modelling option that reduces the liturgy (you don’t have to define schemas) and increases flexibility (store, all sorts of data without prior definition).
Enter native support for JSON in Oracle NoSQL Database – that provides the scalability and flexibility together, which makes developers more productive.
Let’s create a simple table called Persons with column person defined as that holds the user profile CREATE TABLE IF NOT EXISTS JSONPersons ( id integer, person JSON, primary key (id) );
And here’s sample data that we want to insert (notice how each users have a different set of fields): { “id”:1, “person” : { “firstname”:“David”, “lastname”:“Morrison”, “age”:25, “income”:100000, “lastLogin” : “2016-10-29T18:43:59.8319”, “address”:{“street”:“150 Route 2”, “city”:“Antioch”, “state”:“TN”, “zipcode” : 37013, “phones”:[{“type”:“home”, “areacode”:423, “number”:8634379}] }, “connections”:[2, 3], “expenses”:{“food”:1000, “gas”:180} } } { “id”:2, “person”: { “firstname”:“Peter”, “lastname”:“Smith”, “age”:38, “income”:80000, “lastLogin” : “2016-10-19T09:18:05.5555”, “address”:{“street”:“364 Mulberry Street”, “city”:“Leominster”, “state”:“MA”, “phones”:[{“type”:“work”, “areacode”:339, “number”:4120211}, {“type”:“work”, “areacode”:339, “number”:8694021}, {“type”:“home”, “areacode”:339, “number”:1205678}, null, {“type”:“home”, “areacode”:305, “number”:8064321} ] }, “connections”:[3, 5, 1, 2], “expenses”:{“food”:6000, “books”:240, “clothes”:2000, “shoes”:1200} } } { “id”:6, “person” : { “mynumber”:5, “myarray”:[1,2,3,4] } }
You can store the above JSON records in a JSON file and use the load utility to insert the records in Oracle NoSQL Database. Alternatively, you can also use the API to “put” the JSON records in the table.
After successful inserting the data, you may need to modify (change field values, add or remove elements from user profile) the user records. And reasonable to say that the modification shall satisfy follow requirements :
*
Shall take place at server: Eliminating the read-modify-write cycle, that is, the need to fetch the whole user row at the client, compute new values for the targeted fields (potentially based on their current values) and then send the entire row back to the server.
*
Shall be thread safe and not lead in race conditions (if updates are happening concurrently from different sessions)
*
Shall be Atomic: Other threads shall see the most up-to-date values.
Enter SQL UPDATE statement that can be used to update the rows. UPDATE statements make server-side updates directly, without requiring a Read/Modify/Write update cycle. Both syntactically and semantically, the update statement of Oracle NoSQL Database is similar to the update statement of standard SQL, but with extensions to handle the richer data model of Oracle NoSQL Database.
Let’s take a look how this works:
Case 1: Changing field values, this is simplest form of update
Let’s say you want to modify user with id 3 and set the field mynumber to 300. We can use the SET clause as shown below : sql-> UPDATE JSONPersons j SET j.person.mynumber = 300 WHERE j.id = 3 RETURNING id, j.person.mynumber AS MyNumber; +—-+———————+ | id | MyNumber | +—-+———————+ | 3 | 300 | +—-+———————+ 1 row returned
Case: 2 Add elements in Array
Let’s say we want to ADD more elements (50 and 100) to the array myarray field for user id =3. And we want to add 50 at 0th position and 100 at the end. Let’s use the ADD clause for the same: sql-> UPDATE JSONPersons j ADD j.person.myarray 0 50, ADD j.person.myarray 100 WHERE j.id = 3 RETURNING *; +—-+——————-+ | id | person | +—-+——————-+ | 3 | myarray | | | 50 | | | 1 | | | 2 | | | 3 | | | 4 | | | 100 | | | mynumber | 300 | +—-+——————-+ 1 row returned
Notice that multiple ADD clauses are used in the query above.
Case 3: Remove elements from Array
Let’s say we want to remove the elements(myarray and mynumber) from the user id=3. Clearly, this user looks different from other users. We can do that using the single UPDATE statement sql-> UPDATE JSONPersons j REMOVE j.person.myarray, REMOVE j.person.mynumber WHERE j.id = 3 RETURNING *; +—-+—————–+ | id | person | +—-+—————–+ | 3 | | +—-+—————–+ 1 row returned
Case 4: Add elements to the Map
Next, let’s say we want to modify the user with id=3 and add the firstname and lastname for this user. Here, we use a single PUT clause that specifies a map with multiple elements: “ sql-> UPDATE JSONPersons j PUT j.person {“firstname” : “Wendy”, “lastname” : “Purvis”} WHERE j.id = 3 RETURNING *; +—-+——————–+ | id | person | +—-+——————–+ | 3| firstname | Wendy | | | lastname | Purvis | +—-+——————–+ 1 row returned
Next, we can specify the age, connections, expenses, income, and lastLogin fields using multiple PUT clauses on a single UPDATE statement: sql-> UPDATE JSONPersons j PUT j.person {“age”: 43}, PUT j.person {“connections”: [2, 3]}, PUT j.person {"expenses”: {"food”: 1100, "books”: 210, "travel”: 50}}, PUT j.person {"income”: 80000}, PUT j.person {"lastLogin”: "2017-06-29T16:12:35.0285”} WHERE j.id = 3 RETURNING *; +—-+—————————————-+ | id | person | +—-+—————————————-+ | 3 | age | 43 | | | connections | | | 2 | | | 3 | | | expenses | | | books | 210 | | | food | 1100 | | | travel | 50 | | | firstname | Wendy | | | income | 80000 | | | lastLogin | 2017-06-29T16:12:35.0285 | | | lastname | Purvis | +—-+—————————————-+ 1 row returned
Conclusion
Oracle NoSQL Database with native support for the JSON datatype makes it easy to store data that doesn’t confirm to rigid schema. In addition, the SQL UPDATE statement provides a simple and intuitive way to add, modify, remove elements from the JSON documents eliminating need to do the Read-Modify-Write cycle.
http://bit.ly/2Gni1Dg #Oracle #NoSQL #DBA
