Some Beginner Tips for Working with JSON - From a Beginner

We are enhancing the Oracle Dev Gym to automatically execute code you type as a solution to a problem, and validate that it works. We are doing this via REST calls to LiveSQL. It's very cool and a lot of fun to put together. Hopefully it will be way more fun for you to see if you can solve our challenges.

Anyway, LiveSQL is going to pass back results via a JSON document, which means that yes, finally, I am going to start working with JSON and learning about Oracle Database JSON functionality.

Yes, I am a total novice at this. Which, I imagine, is the case for many other PL/SQL developers. So I thought I would immediately share some early lessons learned. That way maybe you will save yourself the 15 minutes I wasted sorting this out.

First, though, if you haven't done anything with JSON yet, start by looking over these:

JSON in Oracle Database
JSON Support in Oracle Database 12c Release 1 (12.1.0.2)

Plus, all the code shown below can be seen, and run, in LiveSQL.

OK. So first I created a table to hold my JSON documents:

CREATE TABLE json_data (my_doc CLOB)
/

Then I insert a row of data. JSON! (or so I thought)

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN 
          DBMS_OUTPUT.PUT_LINE ('Hello JSON!'); 
       END;"}^');

   COMMIT;
END;
/

Use the JSON_VALUE function to get the value for a given property name.

SELECT json_value (j.my_doc, '$.without_spaces') r
  FROM json_data j
/

OK

I can also enclose the property name inside double quotes:

SELECT json_value (j.my_doc, '$."without_spaces"') r
  FROM json_data j
/

OK

Now let's the value for a property name that contains spaces:

SELECT json_value (j.my_doc, '$.with spaces not a good idea') r
  FROM json_data j
/

ORA-40442: JSON path expression syntax error ('$.with spaces not a good idea')

Ugh. OK, so if I am going to put spaces in my names I must enclose that name within double quotes in my call to JSON_VALUE:

SELECT json_value (j.my_doc, '$."with spaces not a good idea"') r
  FROM json_data j
/

Not OK

Fine. Now let's get that PL/SQL block back from my table - notice, I remembered to include the double quotes:

SELECT NVL (json_value (j.my_doc
                      , '$."carriage returns definitely bad idea"'), 'NULL') r
  FROM json_data j
/

NULL

Nothing there! Nothing there? But there is something there. What's going on?

So now I waste time trying to figure it out - but you get to save yourself the time by reading my blog! The problem is that you cannot have carriage returns or newline characters in your JSON values. In other words, that "JSON document" I inserted isn't even valid JSON. And I didn't realize it, because I was lazy and did not tell Oracle Database that the my_doc column contains JSON.

Let's do that:

DELETE FROM json_data
/

ALTER TABLE json_data
ADD CONSTRAINT json_data_json_chk
CHECK (my_doc IS json) enable
/

And  now when I try to insert this value....kaboom!

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN 
          DBMS_OUTPUT.PUT_LINE ('Hello JSON!'); 
       END;"}^');

   COMMIT;
END;
/

ORA-02290: check constraint (JSON_DATA_JSON_CHK) violated ORA-06512: at line 2

OK. So I replace the newline characters with "\n", a standard replacement string to indicate a new line. In the code below you see "\\" because the first \ escapes the next one.

BEGIN
   INSERT INTO json_data
        VALUES (q'^{
          "without_spaces" : "OK",
          "with spaces not a good idea" : "Not OK",
          "carriage returns definitely bad idea" : 
      "BEGIN\\nDBMS_OUTPUT.PUT_LINE ('Hello JSON!');\\nEND;"}^');

   COMMIT;
END;
/

And now....no problems!

SELECT json_value (j.my_doc, '$."carriage returns definitely bad idea"') r
  FROM json_data j
/

BEGIN\nDBMS_OUTPUT.PUT_LINE ('Hello JSON!');\nEND;

And so to conclude:

1. When stuffing JSON into a table's column, make sure you use the IS JSON check constraint to ensure valid JSON.
2. Don't put spaces in your property names. Use underscores or camelCase.
3. Keep newline characters out of your JSON documents.