Browsing JSON Docs with SODA Filters in SQL Developer Web

When using our JSON feature in SQL Developer Web, we make it fairly easy to ‘query’ the data in your JSON collections. We sometimes refer to this as Query By Example (QBE).

{“year”: 1977} : if this is found in the Document, it’s retrieved by the request.

The more accurate way of describing this would be as a FILTER. But doing database and SQL for a couple of decades, my brain turns everything into a query, more on that later.

As you can imagine, our JSON documents can get quite complicated, and our filters grow in complexity as a result.

Let’s take a look at some examples to demonstrate this powerful feature in SQL Developer Web.

Our Sample JSON Document Collection

SQL Developer Web interrogates my JSON and builds this diagram, automatically.

And in this collection, I have 3800 documents. Here’s one of those documents now:

{
    "studio": null,
    "title": "Tangerine",
    "summary": "Tangerine is a 2015 American comedy-drama film directed by Sean Baker, and written by Baker and Chris Bergoch, starring Kitana Kiki Rodriguez, Mya Taylor, and James Ransone. The story follows a transgender sex worker who discovers her boyfriend and pimp has been cheating on her. The film was shot with three iPhone 5S smartphones.   Tangerine premiered at the 2015 Sundance Film Festival on January 23, 2015. It had a limited release on July 10, 2015, through Magnolia Pictures. It received critical acclaim for its screenplay, direction, performances and portrayal of transgender individuals.",
    "sku": "SGE33805",
    "list_price": 0,
    "year": 2015,
    "awards": null,
    "runtime": "+88",
    "gross": 924793,
    "cast": [
        "Kitana Kiki Rodriguez",
        "Mya Taylor",
        "James Ransone",
        "Scott Krinsky",
        "Clu Gulager"
    ],
    "movie_id": 2960,
    "crew": [
        {
            "job": "executive producer",
            "names": [
                "Jay Duplass"
            ]
        },
        {
            "job": "director",
            "names": [
                "Sean Baker"
            ]
        },
        {
            "job": "screenwriter",
            "names": [
                "Sean Baker"
            ]
        }
    ],
    "main_subject": null,
    "nominations": null,
    "budget": 100000,
    "opening_date": "2015-01-01",
    "image_url": "https://upload.wikimedia.org/wikipedia/en/e/e5/Tangerine_%28film%29_POSTER.jpg",
    "genre": [
        "Comedy",
        "Family",
        "Drama"
    ],
    "wiki_article": "Tangerine_(film)",
    "views": 407
}

But I don’s want to see all 3800 documents, or be forced to page through them. I want to pull up specific documents that satisfy my filtering conditions.

I like Horror movies, but not everyone likes those. However, almost everyone enjoys movies directed by Steven Spielberg. So let’s go through those filtering conditions as a couple of examples.

Horror Movies (a simple array)

One of the attributes in our document above, is an array called “genre”. If I simply type that into our Filtering editor…

I’ve type ‘gen’, and the genre array was identified as a possible match.

This wll auto-complete to:

{"genre": []}

But, what I want to see are only the movies that have “Horror” listed as member of said genre array. The function I will need to look at is $IN.

So if I start over and begin with $IN – the editor will auto-complete to something like this:

I find it sometimes helpful to think ‘backwards’ when building my JSON filters.

When I choose one of the functions to complete, we give you some pseudo values as an example.

{"address.zip": {
    "$in": [
        90001
    ]
}}

My movies collection doesn’t look like that, but I can easily substitute what I need. I’m not interested in “address.zip”, no, I want that “genre” array.

It really helps to know your data model. Yes, there’s ALWAYS a data model.

Movies directed by Steven Spielberg

This one is a bit more complicated. We have a crew array, and that has a list of job titles followed by another array of people that did that job.

Now, generally, or almost always, the film police only allow one name to be attached to the director’s credit for a movie, and that will make things a bit easier for us.

Let’s look at this portion of our ‘schema’:

"crew": [
        {
            "job": "executive producer",
            "names": [
                "Jay Duplass"
            ]
        },
        {
            "job": "director",
            "names": [
                "Sean Baker"
            ]
        },
        {
            "job": "screenwriter",
            "names": [
                "Sean Baker"
            ]
        }
    ],

So our filter is going to have two conditions:

job is director
name for said job is in Steven Spielberg

When building multiple conditions for your filter, you generally start with the AND/ORs, and then list the conditions as items of an array.

You can see this pattern in the JSON SODA docs:

We also want to do an AND, so let’s start there.

And…crew = director, name = Steven Spielberg, easy, right?

At first glance this does appear to work. But what I’m actually asking for is ANY document that has both of those attributes/array items.

Which means this matches (Transformers!):

Both things I’m looking for are there…but not quite in the manner I was hoping.

I have to admit, this stumped me for awhile. I couldn’t figure out how to tell the system (SODA) that I wanted those items to match on the SAME array item.

But, then I reached out for help, and Loic suggested my answer was much simpler than I thought it would be. We don’t need an $AND operator at all.

This kind of check is known as a ‘Nested Condition Clause’ (Docs).

You use a QBE nested-condition clause to apply multiple conditions at the same time, to array elements that are objects.
The path targets a parent object whose value is a child object that satisfies the nested condition. If the parent path ends with [*], then it targets a parent object whose value is either such a child object or an array with such an object as at least one of its elements. The latter case is typical: you end the parent path with [*].
All fields contained in the nested condition are scoped to the parent object. They act as multiple conditions on each of the array objects (or the single child object, if the parent’s value is not an array).
SODA Docs 5.2.4 Nested-Condition Clause (Reference)

Which means all I needed to filter on was this:

{
	"crew[*]": {
		"job": "director",
		"names": "Steven Spielberg"
	}
}

JSON Web Interface Features Featured in this Post

Collection Diagram: this shows a visualization of the schema your JSON collection describes.

Zoom, pan-and-scan, download to a local image…make sense of your JSON docs.

Insight/Completion: Ctrl+Spacebar will bring up a list of functions or JSON document attributes/arrays/objects.

Typing is overrated. Let us do that for you.

Double-click Document Viewer/Editor: The results of your applied filters by default show the first few bytes of a JSON document. When you double-click in a document, we then pull up the entire thing, so you can view/edit it.

Full-featured editor (including Command Pallete) for your JSON documents.

Filter History: Whilst I didn’t show this directly, I use it a LOT. Every filter or QBE you’ve applied to a collection, we keep in your browser, for easy recall.