Earlier this week I showed how to use the new Indexing ‘wizard’ to make your JSON collections faster to access. Today I’m going to show you how to make them easier to work with – IF you like SQL.
I mean, who doesn’t like SQL?
The idea is simple.
You like a flexible schema, so you store everything as a JSON document.
But, someone is asking for a SQL interface to the data inside those JSONs.
Luckily for you, Oracle Database allows you define a normal, relational VIEW over top your JSON collections!
And now our Database Actions JSON UI gives you a wizard to make this as easy as !
Step 1: Upgrade to ORDS 21.2
It’s a new feature, you need version 21.2.
If you’re in the Oracle Cloud using one of the Autonomous Services, those have all been upgraded to version 21.2, so you’re good there.
Step 2: Open the JSON interface.
And extended step 2…find your collection, click on it. Then click on the new ‘Create View’ button.
Step 3: Pick your VIEW name, attributes, and associated column names
Each item in the dialog here will be implemetned as a column in the VIEW.
You can:
- select one or more attributes and use the Up/Down arrow to re-order them for the view
- remove them if you don’t need them
- change the Column name if you don’t like what we’ve chosen FOR YOU
- change the Length of the string fields
Step 4: Review the DDL and Test the Query
Click the ‘Test Query’ button:
If you don’t like something, click on the ‘Definition’ button to toggle back to the original screen and make your changes. Repeat, rinse, etc.
Step 5: Click ‘Create’
Step 6: Use the View
Open the navigation menu, click on ‘SQL’ – use your SQL skills!
But Jeff, I want to make some tweaks…
When you get to the Test phase – take our generated SQL/DDL, and change it to be whatever you WANT.
The real value here is that the Database Actions UI is helping you:
- define the JSON $.stuff.more_stuff[*] QBE patterns
- default your column names to something valid
- size the columns properly
- save you a lot of typing
- get you to the SQL world as quickly as possible
