Some Tips for Working with JSON in Oracle Database

View All Scripts Login to Run Script

Script Name Some Tips for Working with JSON in Oracle Database
Description As of 12.1, Oracle SQL offers some great functionality for working with JSON data. But there are a few things to keep in mind: (1) JSON names should not contain spaces. You can deal with it, but you're better off not having to. (2) JSON values cannot contain new lines / carriage returns. (3) Use the IS JSON check constraint on columns containing JSON to ensure that bad JSON doesn't get in. Related blog post: http://stevenfeuersteinonplsql.blogspot.com/2016/12/some-beginner-tips-for-working-with.html
Area SQL General / JSON
Contributor Steven Feuerstein
Created Wednesday December 14, 2016

Statement 1

CREATE TABLE json_data (my_doc CLOB)

Table created.

Statement 2

It's new to me, so I just kind of stumbled along, putting spaces in the JSON attribute names, pasting in text with carriage returns for values. Why not?

The JSON Newbie At Work

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;

1 row(s) inserted.

Statement 3
Since this name has no spaces, I can reference it after the dot without double quotes.
Get a JSON Value
```
SELECT json_value (j.my_doc, '$.without_spaces') r 
  FROM json_data j
```
```
R
OK
```

R
OK

Statement 4

Works with double quotes, too.

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


R
OK

R
OK

Statement 5

Whoops. If there's a space in there, I HAVE to use double quotes.

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')

More Details: https://docs.oracle.com/error-help/db/ora-40442

Statement 6

OK, now I get my value.

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


R
Not OK

Statement 7
Even with double quotes, I get back NULL. This really really confused me for a while. Dan McGhan helped me out though. He said: "Huh, I don't think you're allowed to have newline characters in those values."
```
SELECT json_value (j.my_doc, '$."carriage returns definitely bad idea"') r 
  FROM json_data j
```
```
R
 - 
```
Statement 8
```
DELETE FROM json_data
```
```
1 row(s) deleted.
```
Statement 9
Then Dan said: "Hey, you should add a constraint to the column so that the database will check the values for JSON validity." So I did that.
Ensure Good JSON Data!
```
ALTER TABLE json_data 
ADD CONSTRAINT json_data_json_chk 
CHECK (my_doc IS json) enable
```
```
Table altered.
```

R
Not OK

R
-

Statement 10

My JSON was bad. From a debugging standpoint, ugh. I could get *some* values from the row using JSON_VALUE, but only up until the bad stuff.

Lo and Behold

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 (SQL_RSTISLHZJFIMPSEDOQMKBXEAW.JSON_DATA_JSON_CHK) violated ORA-06512: at line 2
ORA-06512: at "SYS.DBMS_SQL", line 1707

More Details: https://docs.oracle.com/error-help/db/ora-02290

Statement 11

Replace those carriage returns and newline characters with "\n" - the standard.

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;

1 row(s) inserted.

Statement 12

All is good.

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


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

Statement 13
```
DROP TABLE json_data
```
```
Table dropped.
```

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

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database