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.

This will open a SLIDER.

Step 3: Pick your VIEW name, attributes, and associated column names

Cheat and click the ‘Add All’ button – then you can remove what you don’t want.

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:

I’ve went ahead and clicked the green ‘Run It’ button in the toolbar to get the results.

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’

When you see this – you’re good.

Step 6: Use the View

Open the navigation menu, click on ‘SQL’ – use your SQL skills!

Looks like a table, smells like a table…

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.

It’s just code, you can change it up to suit your needs.

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

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment