Searching JSON Arrays with JSON_EXISTS and REST APIs

This is a follow-up to this post, “Retrieve DISTINCT list of values from JSON arrays via SQL” – while it’s not mandatory you read that first, I highly recommend it.

TL;DR summary

I have a table called, ‘MOVIE,’ and in that table are one or more JSON columns.

The ‘raw’ data looks like this –

I want a REST API to search my movies, by GENRE

And actually, I want to be able to pass 2 GENRES, and find those that match, BOTH.

So for example, what movies are BOTH a ‘Horror,’ AND a ‘Comedy.’

The SQL to do this isn’t very hard.

select *
from   movie
where  json_exists(genres, '$[*]?(@ == "Horror")');

This is for a single GENRE, and it’s basically saying, are there ANY positions in the array that match on the string, “Horror”

Pivoting that SQL to feed a REST API

We need:

• module and template, URI : movies/genre/:type
• handler: GET
• SQL: see above

So we end up with this :

This won’t work. We can’t use SQL binds inside of a string.

So what do we do?

The JSON Developer’s Guide discussion on the JSON_EXISTS function makes the answer very clear, and easy to implement!

“The optional filter expression of a SQL/JSON path expression used with json_exists can refer to SQL/JSON variables, whose values are passed from SQL by binding them with the PASSING clause”

It turns out that’s exactly what we need – to pass a SQL variable, via a BIND.

So let’s look at our handler, again, using the PASSING clause.

select *
  from movie
 where json_exists(genres, '$[*]?(@ == $v1)' -- $v1 is arbitrary
                    PASSING :type as "v1")   -- v1 is derived from '$v1'

And let’s plug that into the REST handler, and try it out!

It works!

Ok, now let’s try matching two tags!

Normally I would submit two tags with a URI structure on the template like:

movies/genre/:type1/:type2

But, there’s another way, and since I’ve demonstrated the bind notation off the URI so many times, I’ll instead demonstrate how to do this with URI parameters.

URI template definition: movies/
    GET request example: movies/?type1=Horror&type2=Comedy

Here’s the handler code:

select *
  from movie
 where json_exists(genres, '$[*]?(@ == $v1)'
                    PASSING :type1 as "v1") AND
       json_exists(genres, '$[*]?(@ == $v2)'
                    PASSING :type2 as "v2")

And here’s what that looks like defined, and running:

But wait, we also need to define two parameters

I’m going to tell the ORDS REST API handler, that it can expect a couple of URL parameters on the URL, and that I want to reference them via specific bind variables in the handler SQL block.

It is NOT an accident that you see ‘:type1’ in the PASSING clause of the SQL function, and also a ‘genre1’ parameter name in the URL.

The way I’ve coded this, those parameters are NOT optional, as in, if i only pass in 1 genre, I get zero hits back.

I don’t consider these predictable, or friendly APIs. It would be better if going to movies/genre/ brought back EVERYTHING, or if I only passed 1 genre, it would match on just the 1 instead of the 1 genre and a NULL.

There’s nothing NEW here

Our JSON technology in the database debuted in 12c (2012), and using ORDS REST APIs with query parameters (or :binds) has been around since the very beginning.

But, I wanted to bring the two topics, together.

I like SciFi and Horror, but my wife likes…