📢Oracle NoSQL Database Version 25.1

[dario-vega]

📢 📢 📢 We are excited to announce the general availability of Oracle NoSQL Database Version 25.1.

It is a flexible, multi-model, multi-region, hybrid-cloud, active-active database designed to provide a highly available, scalable, performant, and reliable data management solution to meet today's most demanding workloads. Focusing on simplicity and developer productivity, deploying in hybrid-cloud or on-premise environments is automated and completed with just a few clicks. In addition, NoSQL's built-in cross-region replication can be enabled in minutes to deploy a fully active-active system.

Oracle NoSQL Database is well-suited for high-volume and high-velocity workloads, such as the Internet of Things, customer 360, online advertising, fraud detection, consumer-scale persistent session management, user personalization, and online gaming. In addition, developers can use a single application interface to build applications that run anywhere quickly.

With our new version - 25.1

• Inner Joins Extended the SQL dialect of Oracle NoSQL Database to support inner joins among tables in the same table hierarchy. Two or more tables in the same hierarchy may be joined in a query.
• Support of JSON Merge Patch Extended the SQL UPDATE statement to support updates of JSON fields via the JSON merge patch specification RFC 7396.
• Support for Updating Multiple Records in a Query An update query can now update multiple records in a single transaction if the query predicate contains a shard key.

We also improved our SDK, utilities, and plugins, which are delivered in specific packages with specific versions.

[dario-vega]

Inner Joins

Extended the SQL dialect of Oracle NoSQL to support inner joins among tables in the same table hierarchy. Two or more tables in the same hierarchy may be joined in a query. There are no restrictions on the position of the tables within the hierarchy. Self-joins (joining a table with itself) are also allowed.

To show some examples of join queries, we use the following tables that model an email application:

create table users(uid integer, user_info json, primary key(uid))
create table users.messages(msgid integer, msg_info json, primary key(msg_id))
create table users.inbox(msgid integer, primary key(msgid))
create table users.sent(msgid long, primary key(msgid))
create table users.deleted(msgid long, primary key(msgid))

The following query selects the messages that (a) belong to users working on NoSQL, and (b) are in the inbox folder of their corresponding user. The join predicates "msg.uid = u.uid" and "msg.uid = inbox.uid" make the query satisfy the above restriction.

SELECT msg.uid, msg.msgid, msg.msg_info
FROM users u, users.inbox inbox, users.messages msg
WHERE msg.uid = u.uid and msg.uid = inbox.uid and
      msg.msgid = inbox.msgid and
      u.user_info.organization = "NoSQL"

The following query returns for each message M in the inbox folder, the number of messages that belong to the same user and the same thread as M.

SELECT msg1.uid, msg1.msgid, count(*)
FROM users.messages msg1, users.messages msg2, users.inbox inbox
WHERE msg2.uid = msg1.uid and
      msg1.uid = inbox.uid and
      msg2.msg_info.thread_id = msg1.msg_info.thread_id and
      msg1.msgid != msg2.msgid
GROUP BY msg1.uid, msg1.msgid

The following query groups each user's inbox messages according to their view date and computes the number of messages viewed per date. Notice that in this query, we have to use the "$" in the alias of the users.messages table. Without the "$," the unnesting expression in the FROM clause would be msg.content.views[], and msg.content would be interpreted as a table name.

SELECT $msg.uid, $view_date, count(*)
FROM users.messages $msg, users.inbox $inbox, $msg.content.views[] as $view_date
WHERE $msg.uid = $inbox.uid and $msg.msgid = $inbox.msgid
GROUP BY $msg.uid, $view_date

[dario-vega]

Support of JSON Merge Patch

Extended the SQL UPDATE statement to support updates of JSON fields via the JSON merge patch specification. For example, assuming that "info" is a column of type JSON in a table called "Foo", the following query updates the info column of the row with id = 0. It sets the value of the "state" field inside the "address" field to "OR". It also sets the value of the "firstName" field to the value of the $name external variable. If any of the targeted fields ("address", "state", and "firstName") do not exist in info, it is inserted.

update Foo f
json merge f.info with patch { "address" : { "state" : "OR" }, "firstName" : $name }
where id = 0

And the following query removes the "city" field, if it exists, from the address of the row with id = 2.

update Foo f
json merge f.info.address with patch { "city" : null }
where id = 2

[dario-vega]

Support for Updating Multiple Records in a Query

Here is an example:

create table users(groupId string,
                   uid integer,
                   name string,
                   level integer,
                   primary key(shard(groupId), uid))

insert into users values("g01", 1, "alex", 0)
insert into users values("g01", 2, "jack", 0)
insert into users values("g02", 1, "flex", 0)

update users set level = level + 1 where groupId = "g01"
{"NumRowsUpdated":2}

select * from users;
{"groupId":"g01","uid":1,"name":"alex","level":1}
{"groupId":"g01","uid":2,"name":"jack","level":1}
{"groupId":"g02","uid":1,"name":"flex","level":0}