Oracle SQL and JSON

Video description

Oracle SQL and JSON

Video description

Introduced as part of Oracle 12, Oracle JSON SQL makes it possible to convert the JSON data world of keys and values into the relational data world of columns and rows. At its core, this course teaches you how to use Oracle JSON SQL.

You'll learn the key JSON SQL functions that tell you: If the JSON data is valid, if a particular element and/or value exists in the JSON data, how to extract a specific scalar value from JSON data, and how to translate JSON keys and values into relational rows and columns. But this is more than a how-to-use JSON SQL course, it's a refresher course for those who need a solid understanding of the distinctions between the relational data world and the JSON data world, and how SQL can act as a bridge between the two.

Explore the SQL methods that work for relational databases and JSON databases
Learn about dot notation and how it's used to navigate through JSON elements
Master the key JSON SQL functions: IS_JSON, JSON_EXISTS, JSON_VALUE, JSON_QUERY, and JSON_TABLE
See how JSON_Table translates JSON keys and elements into relational rows and columns
Learn about JSON indexes in comparison to relational indexes
Understand how Oracle handles bad JSON data with the ERROR clause
Watch how the Oracle optimizer translates JSON SQL into an effective query plan

Darryl Hurley is a senior level database engineer with Change Healthcare who specializes in Oracle database administration and PL/SQL programming. He has used SQL with Oracle since Oracle version 5, and speaks at Oracle User Group conferences. Darryl blogs at implestrat.com, and is the author of the O'Reilly title "Learning Oracle PL/SQL".

Publisher resources

Download Example Code

Introduction

Welcome To The Course 00:04:08

About The Author 00:01:06

JSON And Relational Data And SQL

Introducing Relational Data 00:02:35

Introducing JSON Data 00:03:04

Course Example Data 00:02:23

Introducing Relational SQL 00:04:28

Introducing JSON SQL 00:03:25

JSON Dot Notation

Introduction To Dot Notation 00:03:05

Query With Dot Notation 00:06:33

Explain Plan For Dot Notation Queries 00:03:01

Comparison To Relational Queries 00:02:54

JSON Conditions

Strict JSON 00:02:52

Unique JSON 00:02:26

Is_JSON Constraint 00:05:42

Comparison To Relational Data 00:02:27

JSON_Exists Function

Introduction To JSON_Exists 00:02:04

SQL Where Clause 00:01:10

Error Clauses 00:02:22

Explain Plan 00:01:09

Comparison To Relational 00:01:42

JSON_Exists Values 00:01:47

JSON_Exists Passing Clause 00:01:54

JSON_Value Function

Introduction To JSON_Value 00:02:01

Error Clauses 00:02:27

Returning Clause 00:02:11

On Empty Clause 00:02:26

Explain Plan 00:01:01

Comparison To Relational 00:02:02

JSON SQL Deep Dive

Deep Dive Introduction 00:02:06

Columns Clause 00:03:04

Return Value 00:02:36

Return JSON 00:02:50

Error Clause 00:05:41

Columns In Where Clause 00:03:04

Wrappers 00:03:32

Nested Rows 00:03:17

Array Values 00:03:16

Explain Plan 00:04:37

JSON_Query Function

Introducing JSON_Query 00:02:49

Returning Clause 00:02:02

Wrappers 00:03:22

Error Clauses 00:01:48

Explain Plan 00:01:33

Comparison To Relational 00:02:21

JSON_Table Function

Introduction To JSON_Table 00:03:41

Columns Clause 00:04:16

Error Clause 00:03:32

JSON Format 00:01:22

Wrappers 00:01:40

Nested Values 00:03:04

Mix And Match Data 00:01:53

Explain Plan 00:03:50

Comparison To Relational 00:01:54

JSON Indexes

Introduction To Indexes 00:02:11

Relational Indexes 00:01:47

JSON Indexes 00:06:00

Explain Plan 00:04:09

Errors Clause

Introduction To Errors Clause 00:02:27

Available Error Clauses 00:02:36

Interaction With SQL Errors 00:02:08

Advice For Error Clauses 00:02:57

Combining JSON And Relational Data

Queries With Both JSON And Relational Data 00:04:48

Oracle Views 00:04:21

Conclusion

Wrap Up And Thank You 00:00:46

Start your Free Trial

Self paced

Go to the Course
We have partnered with providers to bring you collection of courses, When you buy through links on our site, we may earn an affiliate commission from provider.