We are pleased to announce the general availability of Oracle NoSQL Database Release 25.1 for the Enterprise Edition (EE) and the Community Edition (CE).
Oracle NoSQL Database is seamlessly integrated into the fabric of our digital world. Whether you’re swiping a credit card, browsing a website, managing IoT devices, playing online games, or using mobile apps, there’s a good chance Oracle NoSQL Database is working behind the scenes. Its global presence enables fast, reliable data processing for many of the applications we rely on every day.
New Feature Highlights
1. INNER JOIN – Enhance Join Operation Support for Table Hierarchies
We’re expanding the join capabilities in Oracle NoSQL Database by adding support for INNER JOIN, complementing the existing LEFT OUTER JOIN functionality for table hierarchies. The INNER JOIN operation returns rows by combining data from two or more tables based on matching values in specified columns. Only rows that satisfy the join condition are included in the result set. In Oracle NoSQL Database, child tables within a hierarchical structure inherit the primary key columns of their parent tables. Additionally, all tables in the hierarchy share the same shard key columns, enabling efficient and scalable join operations.
To illustrate the behavior of both INNER JOIN and LEFT OUTER JOIN, we’ll use a hierarchical table example based on an airline baggage tracking use case below. This example will demonstrate how each join type operates and the differences in the results they produce.
Example 1: INNER JOIN – Retrieve Bag Details for a Specific Ticket
In this example, we use an INNER JOIN to fetch all bag details associated with a specific ticket number. The parent table ticket is joined with its child table bagInfo, and a filter is applied to limit the result to ticket number 1762324912391. Only a record that matches the join condition and the filter criteria is returned. The SQL syntax is clear and concise, making it easy to query related data within a table hierarchy.
SELECT * FROM ticket a ticket.bagInfo b WHERE a.ticketNo=b.ticketNo AND a.ticketNo=1762324912391 |
Query Result
{"a":{"ticketNo":1762324912391,"confNo":"LN0C8R"}, "b":{"ticketNo":1762324912391,"id":79039899168383,"tagNum":1765780623244, "routing":"MXP/CDG/SLC/BZN","lastActionCode":"OFFLOAD","lastActionDesc":"OFFLOAD", "lastSeenStation":"BZN","lastSeenTimeGmt": "2019-03-15T10:13:00.0000Z","bagArrivalDate":"2019-03-15T10:13:00.0000Z"}} |
Example 2: LEFT OUTER JOIN – Retrieve Bag Details for a Specific Ticket
This example uses a LEFT OUTER JOIN to retrieve all bag details for a given ticket number. The query structure is similar to Example 1 but uses a LEFT OUTER JOIN along with the ON clause. The result set includes all rows from the ticket table that satisfy the ON condition. If there are no matching rows in the bagInfo table, those fields are returned with NULL values—ensuring that ticket records are included even when bag details are missing.
SELECT * FROM ticket a LEFT OUTER JOIN ticket.bagInfo b ON a.ticketNo=b.ticketNo AND a.ticketNo=1762324912391 |
Query Result
{"a":{"ticketNo":1762355527825,"confNo":"HJ4J4P"},"b":null} |
Benefits of INNER JOIN
- Performance Efficiency: INNER JOINs are generally faster because they return fewer rows and require less processing. The database engine doesn’t need to handle NULLs or pad unmatched rows.
- Cleaner Results: INNER JOINs return only rows with valid relational matches, eliminating NULLs introduced by unmatched records. Easier to work with in aggregations, filters, and reporting.
- Logical Simplicity – INNER JOINs are conceptually simpler when you only care about matched data. Ideal for enforcing referential integrity (e.g., finding orders with valid customers).
2. JSON Merge Patch – Efficient Partial Document Update
It is a lightweight approach to update parts of a JSON document. Instead of sending the entire updated document, you send only the changes you want to make. It’s defined in RFC 7386. A JSON Merge Patch document specifies modifications to a target JSON structure using a format that closely resembles the original document. The recipient interprets the intended changes by comparing the patch content with the current state of the target. Properties present in the patch but absent in the target are added; existing properties are updated with new values. Additionally, any property explicitly set to null in the patch signifies a request to remove that property from the target document.
Example: JSON Merge Patch
This example illustrates the JSON MERGE PATCH clause to update the values in the last leg of the passenger’s travel itinerary. To calculate the last element of the flightLegs JSON array, it uses the built-in size function, which returns the number of travel segments and subtract it by one. In the patch expression, it supplies the fields to be updated. Here, it modifies the flightNo value, removes the actions field by supplying a NULL value, and inserts a new JSON document tempActions in the flightLegs JSON array.
|
UPDATE ticket b |
Benefits
- Optimized for Partial Updates: By allowing clients to transmit only the fields that require modification, JSON Merge Patch significantly reduces the size of update payloads. This leads to improved network efficiency and faster processing.
- Concise and Intuitive Syntax: JSON Merge Patch utilizes standard JSON formatting, making it straightforward to construct and interpret. Its simplicity reduces the cognitive load for developers and minimizes the potential for errors. Native support for field deletion by setting a field’s value to null effectively removes it from the target document. This approach eliminates the need for explicit delete operations and simplifies the update logic.
- Efficient Object Replacement: Nested objects can be replaced in their entirety with minimal effort.
3. Multiple Record Update in a Query
It supports a query that update multiple records in a single transaction if the query predicate contains a shard key. All relevant rows are updated within the scope of a single transaction, either all rows will be updated successfully, or none of them will be updated
Example:
The following example creates a table named Employee with a shard key named Department. The shard key sets the scope for the transactional update.
|
CREATE TABLE Employee (
empID INTEGER, department STRING, fullName STRING, info JSON, PRIMARY KEY(SHARD(department), empID) |
The following example updates the salary and performance rating for product managers (PM) based in Cupertino, increasing their salary by $20,000 and setting their performance rating to “EXCEPTIONAL”.
|
UPDATE Employee emp |
Benefits
- Data Consistency & Atomicity: Transactional Integrity: If wrapped in a transaction, the update is atomic—either all rows are updated or none, preventing partial updates.
- Handles Bulk Operations Better: As data grows, single-query updates scale more efficiently than iterative ones.
- Performance Efficiency: Reduced network overhead with one query means fewer round-trips between your application and the database server.
- Lower Resource Usage: Minimizes CPU and memory consumption compared to executing multiple queries.
Oracle NoSQL Database in a Nutshell
Oracle NoSQL Database is a flexible, multi-model, multi-region, hybrid-cloud database built to support active-active deployments. It delivers high availability, scalability, performance, and reliability—making it ideal for today’s most demanding workloads. Designed with simplicity and developer efficiency in mind, it can be deployed in hybrid-cloud or on-premises environments with just a few clicks. Built-in cross-region replication enables fully active-active systems to be set up in minutes
Optimized for high-volume, high-velocity use cases, Oracle NoSQL Database powers applications in areas such as IoT, customer 360, online advertising, fraud detection, large-scale session management, personalization, and online gaming. Developers benefit from a unified application interface that allows them to build and run applications seamlessly across environments.
Learn more about Oracle NoSQL Database and Oracle NoSQL Database Cloud Service.