the-json-expedition

The Oracle PLSQL JSON_OBJECT function assembles JSON KEY:VALUE pairs. It uses column values from a table or view as the VALUE. It can use hardcoded strings or column values as the KEY.  Here is an example creating 3 records in a table and querying them using the JSON_OBJECT function. — record 1 NOT JSON INSERT … Read more

JSON Path Expression Item Method’s (PEIM) were added in Oracle 18c. They provide extensions to interpret JSON data. But the interpretations are stricter than they appear. Take the string() PEIM for instance – the Oracle documentation says it returns an “SQL VARCHAR2(4000) interpretation of the targeted scalar JSON value”. That seems straightforward but… — Create … Read more

Dataguide provides three attributes for JSON data – the path, the datatype and the length. Ever since Oracle 12.2 it has done that dutifully – even for arrays of scalar (non key-value pair) values. But initially it only denoted the fact there was an array, it did not denote the datatype of the entries in … Read more

The previous post used numeric and string value to demonstrate how Oracle JSON Data Guide may change assigned datatypes to accommodate mixed data. This post demonstrates the same concept using JSON boolean values. SQL> INSERT INTO json_data 2 VALUES(1,'{“RAPTOR_LOST”:true}’); 1 row created. SQL> INSERT INTO json_data 2 VALUES(2,'{“RAPTOR_LOST”:”YES”}’); 1 row created. SQL> SELECT JSON_DATAGUIDE(json_col) boolean … Read more

The JSON Data Guide provides a parseable list of elements (including name, datatype and length) contained JSON data. This makes parsing much faster. by eliminating the need to interrogate for structure and then get the desired data from it. But Data Guide has a few nuances to be aware of. And the first is that … Read more

Dataguides provide a map of JSON data and facilitate quick access to the elements contained within. I’ve demonstrated that in several other posts. This post expands on those concepts to show how Dataguides can be used to validate JSON as well. What do I mean by validate? Good question – I mean ensuring that JSON … Read more

During the February 2018 ODTUG Code Talk about JSON and PLSQL an attendee asked the following question: Can we validate JSON using JSON-schema? (similar to XML-schema)? I did not have enough information at the time to answer the question so I did some research afterwards and found that XML schema’s have three basic requirements: 1. … Read more

My February ODTUG Code Talk about Oracle PLSQL and JSON spawned many great questions from the attendees. I’ll answer them in this series of posts. The first two of questions asked were: can you validate data being stored Can you create constraints? I’ll answer them both below. First up is an example of validating JSON … Read more

The previous post in this series demonstrated recursion to parse JSON data in PLSQL. It assumed the JSON passed in would start with key-value pairs. But JSON is not always that predictable. For example an array of key-value pairs could be passed in. Lets see what changes that requires. First I’ll add 2 new rows … Read more

JSON data can take on almost any structure of arrays, key value pairs and objects. It can even contain embedded arrays, key-value pairs and objects. Thus parsing it without knowing the structure up-front can be painful. One way to conquer that pain is with recursion which PLSQL is really good at. My Daughter – What … Read more