Скачать или смотреть How to Use JSON_TABLE in Oracle 12C to Retrieve Data When JSON Column is NULL

Learn how to effectively retrieve other columns from a table using `JSON_TABLE` in Oracle 12C, even when the JSON column contains no data.
---
This video is based on the question https://stackoverflow.com/q/63342069/ asked by the user 'AnuC' ( https://stackoverflow.com/u/13518431/ ) and on the answer https://stackoverflow.com/a/63342814/ provided by the user 'nfgl' ( https://stackoverflow.com/u/12111821/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Oracle 12C - JSON_TABLE - How to return other columns in the table when json column is null

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Resolving JSON Column Retrieval Issues in Oracle 12C

When working with databases, it's common to encounter challenges with retrieving data, particularly when dealing with JSON data types. One such issue arises when trying to extract information from a JSON column that could potentially store null values or be empty. In this post, we'll discuss a specific scenario involving the JSON_TABLE function in Oracle 12C, focusing on how to retrieve additional columns even when the JSON column contains no entries.

Understanding the Problem

In our example, we have a STUDENT table that includes a large JSON column (PAYMENT_JS), structured to hold various payment details for students. Here’s a brief overview of the relevant table structure:

[[See Video to Reveal this Text or Code Snippet]]

The PAYMENT_JS column is a CLOB type, which contains JSON data structured as follows:

[[See Video to Reveal this Text or Code Snippet]]

The challenge arises when the PAYMENT_JS column is empty for certain records. When this occurs, the standard SQL query fails to return results for the NAME and ADDRESS fields associated with those entries. This leads us to the core of our inquiry: How can we retrieve data from other columns in the table when the JSON column is null?

Proposed Solution: Using LEFT JOIN with JSON_TABLE

To tackle this problem effectively, we can utilize a LEFT JOIN rather than combining multiple tables in the old-fashioned cartesian product syntax. The LEFT JOIN ensures that all records from the first table (the STUDENT table) are returned, while still attempting to match entries in the JSON table. If no corresponding JSON data is found, the query will return null for the JSON fields but still provide valid data for the other columns.

Here's how the adjusted SQL query looks:

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Query Components

LEFT JOIN: This ensures that even if there's no matching JSON data, the STUDENT entries will still be returned.

JSON_TABLE: This function is used to extract specified fields from the JSON data. The NULL ON EMPTY specification means that when there are no matching entries in the JSON column, it will not return an error but will insert null values for the respective fields.

ON 1 = 1: This is a common trick used in SQL to create a simple join condition that evaluates to true, allowing the LEFT JOIN to operate as intended.

Expected Output

With the modified query structure, you can retrieve data even if the JSON column is empty. For instance, if we query the STUDENT table, the output should appear as follows:

[[See Video to Reveal this Text or Code Snippet]]

In this output, you can clearly see that while Kavya has no JSON records, her name and address are still displayed, thanks to the LEFT JOIN approach.

Conclusion

Using JSON_TABLE effectively in Oracle 12C requires careful handling of null or empty values. With the suggested LEFT JOIN method, you can ensure a robust query that retrieves all necessary details, even when JSON data is not present. This approach not only maintains data integrity but also enhances the usability of your SQL queries.

By implementing these strategies in your Oracle database, you'll be better equipped to manage and extract structured data from JSON applications seamlessly, ensuring that no valuable information is lost in the process.