Working with {JSON} in SQL Developer Web

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.

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!

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!

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.

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.

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.

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.

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!