SQL Developer Web has over the last 6 months been aggressively adding and enhancing support for working with the JSON in your Oracle Database. Catch up here.

Browsing my JSON documents in SQL Developer Web

I wanted to talk to you today about:

  1. how to sort your JSON Docs
  2. how to do text searches for any attribute in your JSON Docs

In SQL, ordering your results is as simple as using the ORDER BY clause, a la –

SELECT * FROM TWEETS
ORDER BY TWEET_ID ASC

The database KNOWS that TWEET_ID is column in my TWEETS table, and is defined as a NUMBER. But, in the JSON Document Store world since the JSON Document defines the schema, you may need to give the database some help when it comes to sorting.

Let’s take a quick look!

JSON ORDER BY’s

I want to see my tweets, ordered by the number of times someone clicked the ‘Favorite’ button.

Current record #HumbleBrag

About a year ago, I downloaded my Twitter personal data, 100,000+ tweets. It comes in a big archive ZIP of JSON files. I used THIS method to upload them, in a single POST to my Always Free Autonomous Database (ATP).

More fun examples, using your own data to learn SQL, build REST APIs, etc.

Untappd || iTunes || Twitter || Strava || Netflix || Spotify


If we look at one of those documents –

{
  "favorited": false,
  "truncated": false,
  "created_at": "Wed Nov 05 16:32:32 +0000 2014",
  "possibly_sensitive": false,
  "lang": "en",
  "retweet_count": "1",
  "retweeted": false,
  "full_text": "Yes, reports over excel exports!  #ECO14 #SQLDev http://t.co/q7T0XMJTta",
  "extended_entities": {
    "media": [
      {
        "expanded_url": "https://twitter.com/thatjeffsmith/status/530034940692688897/photo/1",
        "indices": [
          "49",
          "71"
        ],
        "url": "http://t.co/q7T0XMJTta",
        "media_url": "http://pbs.twimg.com/media/B1sP9yTCMAEDski.jpg",
        "id_str": "530034935294210049",
        "id": "530034935294210049",
        "media_url_https": "https://pbs.twimg.com/media/B1sP9yTCMAEDski.jpg",
        "sizes": {
          "medium": {
            "w": "1200",
            "h": "679",
            "resize": "fit"
          },
          "thumb": {
            "w": "150",
            "h": "150",
            "resize": "crop"
          },
          "small": {
            "w": "680",
            "h": "385",
            "resize": "fit"
          },
          "large": {
            "w": "1344",
            "h": "760",
            "resize": "fit"
          }
        },
        "type": "photo",
        "display_url": "pic.twitter.com/q7T0XMJTta"
      }
    ]
  },
  "display_text_range": [
    "0",
    "71"
  ],
  "source": "<a href="\&quot;http://www.myplume.com/\&quot;" rel="\&quot;nofollow\&quot;">Plume&nbsp;for&nbsp;Android</a>",
  "id": "530034940692688897",
  "favorite_count": "3",
  "id_str": "530034940692688897",
  "entities": {
    "user_mentions": [],
    "urls": [],
    "symbols": [],
    "media": [
      {
        "expanded_url": "https://twitter.com/thatjeffsmith/status/530034940692688897/photo/1",
        "indices": [
          "49",
          "71"
        ],
        "url": "http://t.co/q7T0XMJTta",
        "media_url": "http://pbs.twimg.com/media/B1sP9yTCMAEDski.jpg",
        "id_str": "530034935294210049",
        "id": "530034935294210049",
        "media_url_https": "https://pbs.twimg.com/media/B1sP9yTCMAEDski.jpg",
        "sizes": {
          "medium": {
            "w": "1200",
            "h": "679",
            "resize": "fit"
          },
          "thumb": {
            "w": "150",
            "h": "150",
            "resize": "crop"
          },
          "small": {
            "w": "680",
            "h": "385",
            "resize": "fit"
          },
          "large": {
            "w": "1344",
            "h": "760",
            "resize": "fit"
          }
        },
        "type": "photo",
        "display_url": "pic.twitter.com/q7T0XMJTta"
      }
    ],
    "hashtags": [
      {
        "text": "ECO14",
        "indices": [
          "34",
          "40"
        ]
      },
      {
        "text": "SQLDev",
        "indices": [
          "41",
          "48"
        ]
      }
    ]
  }
}

We can see the way Twitter stores a Tweet has a pretty interesting ‘schema’ – and it gives me a lot of fun queries I can run.

Let’s look at a couple of attributes as examples –

In this ‘data model’, the ID is a number, that’s being stored as a string…

So I have the ‘meat’ of the Tweet, stored as “full_text”, and then I have some other interesting attributes like the Tweet ID and even, how many times it’s been retweeted or favorited.

Simple {abbreviated} Order By Syntax

I can very simply say I want the results ordered by “favorite_count”, DESC. It’s fun for my EGO to sort the Tweets in order of their popularity – and also it’s easy to see the discrepancy of doing a sort by number vs by text.

In the editor, use this text to define a ORDER BY

{
 "favorite_count": -1
}

It’s a JSON document, with the attributes followed by a 1 or -1. 1 is a normal/default sort of ASC, and -1 is a DESC.

So by simple sort syntax, the highest number of favorites a Tweet of mine has reached is 9???

This seems FISHY to me! I know my data, so I know enough to know that’s not ‘right’ – or, perhaps I’m not asking in the correct fashion. This is a case where the SODA APIs have to construct SQL for the database, and at the time of the call, there’s not enough KNOWN about the documents to give the database a heads-up that it’s about to be working with NUMBERs.

Thankfully, there’s a solution!

Advanced {array} Order By Syntax

So instead of relying on the optimizer to ‘guess’ or infer what the TYPE, we can simply tell it, at the time we issue the query.

[
  {
    "path": "favorite_count",
    "datatype": "number",
    "order": "desc"
  }
]

Running the query again, the results are QUITE different.

9 > 152 as a string, but 152 > 9 as a number 🙂

So what happens behind the scenes?

If we make our call manually, I can ask the SODA for REST API to include the SQL that was used to satisfy the request. So we can go from SODA to SQL!

At the bottom of the response payload is the magic ‘sqlStatement!”

The magic SQL, is:

SELECT /*+ FIRST_ROWS(101) */ "JSON_DOCUMENT"
     , "ID"
  FROM "ADMIN"."thatjeffsmithTweets"
 ORDER BY JSON_VALUE ( "JSON_DOCUMENT" format oson, '$.favorite_count' returning NUMBER error ON
       ERROR NULL ON empty
) DESC, "ID"
fetch NEXT 101 ROWS ONLY

The most important keywords here are ‘returning number’.

SELECT * FROM is B-O-R-I-N-G!

My QBE strings in these examples have all been ‘{}’ – which will MATCH ALL documents in my collection.

What if…I wanted to know what tweets were favorited most where I mentioned ORDS?

All I need to do is first create a JSON SEARCH INDEX (text), and then I can run some very simple (yet powerful) queries across my collection documents.

I’ve already created the INDEX, now onto the QBE. For the “full_text” attribute use the contains function.

So let’s do the search, but let’s look for any mention of the words “ORDS” in the text field for my tweets..and let’s order them in descending order of the number of times they’ve been favorited. Added bonus, these aren’t case-sensitive!

{
  "full_text": {
    "$contains": "ORDS"
  }
}
Note my Twitter data dump is about a year old.

I tweet about all kinds of things, so I can look for where I tweeted about my favorite movies or beer or…

My favorite movie, or always shows up on my Top 10 lists.

Resources / Documentation Links

There’s a lot of technology bits included in this blog post, here’s a list of everything covered:

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.

Write A Comment