Brand new for SQL Developer Web version 20.3 – a complete interface for working with your JSON Collections in the Oracle Database!

IF you have:

  • ORDS 20.3
  • Database 19c (or higher)
  • The SODA_APP Role…

…you’ll see the JSON card in SQL Developer Web.

Click the card, or use the Hamburger Button to navigate

So, what all can we do with JSON?

SQL Developer Web’s JSON feature will allow you to :

  1. Create and delete collections
  2. Create, update, and delete documents
  3. Query By Example (QBE) or filter those documents
  4. Create and visualize JSON Data Guides

In addition to the main features, we also have a nice JSON Editor itself – to help you avoid silly {JSON} syntax problems, use Forms for creating and editing documents, search your docs, and much more.

And finally, we have integrated help and a built-in Desktop Tour to help you get started!

Click the binoculars button to start the tour!

Creating a Collection

This dialog has quite a bit of metadata you can provide, but the only real requirement is a name – and yes, those are case-sensitive!

You may want to switch to a GUID for your Document IDs…
Success!

Once you. hit ‘Create’ you’ll see your request is submitted, and you should also be welcomed by this confirmation dialog in the upper right hand corner.

You’ll also see your new collection listed to the left. Click on a Collection will bring up the Query By Example (QBE) interface. We’ll also list all the documents in your collection, by their ID.

The new collection will be empty, so we’ll need to create a new document.

Adding a Document

Click the ‘New Document’ button in the toolbar. Then you’ll be presented with a Text view of your JSON.

The new JSON editor catches my syntax issues – I need this help!
In Form mode, you can’t change the attribute names, but you can change the values.

Click ‘Create’ and our new Document will be added to the collection.

Now, let’s say I have MANY documents already, and I need help finding one or more in particular.

Don’t tell anyone, but I like simple integers for my ID’s 🙂

In this case, I have 70 documents. We can use the Query by Example Feature (QBE) to filter the list.

Query By Example (QBE)

The {} in the Query box says, ‘show me everything!’

I’m using some examples we ship with the SODA for REST feature in ORDS. It includes some JSON documents and some QBE strings we can use for filtering.

The QBE input is a JSON ‘fragment’ that’s used for pattern matching our existing documents.

{
  "$and": [
    {
      "LineItems.Part.UPCCode": "13023015692"
    },
    {
      "LineItems.ItemNumber": 3
    }
  ]
}

This one is pretty simple, but basically give me documents where we have this UPC Code and where we have a ItemNumber of 3 for Line Items.

Input JSON for QBE, hit the ‘Go’ button – select document, view or edit.

Diagramming the Documents

JSON Data Guides are very interesting. They give a sort of blue print to your JSON Documents. Some folks say JSON is popular because it’s ‘schema-less’ but I would say it’s popular because the schema is defined in your application code instead of the database. In either scenario, there’s always a schema.

This button makes your document a bit easier to see what’s going on – if you like pictures.

What’s this document look like, really?

More features coming soon!

The next version of the JSON interface will feature a QBE ‘query history’ panel, a much nicer editor interface for those queries, the ability to upload multiple files to a collection, and the ability to delete all the documents for a give QBE.

These features are also coming to the Autonomous Database platform, soon!

TL;DR – The Movie!

A dozen minutes or so…
Author

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

3 Comments

    • Dmytro Nickolaevich Grytskiv

      Sorry, I see a Note in documentation that says that it requires 19C or later version.

    • Yeah the API changed dramatically from 12.1 to 19, so we set 19 as the min.

      Note that 12.1, 12.2, and 18 are no longer supported by Oracle.

      Everyone is encouraged to upgrade to 19c.

Write A Comment