Generate JSON Data and Perform Schema Validation in Oracle

Problem:

You want to generate JSON data and perform Schema Validation in Oracle.

Solution

Functions like JSON_OBJECT, JSON_ARRAYAGG, JSON_QUERY can be used to generate a complex json data using mutiple columns and tables.

JSON_OBJECT:- Its an SQL/JSON function. JSON_OBJECT takes as its input one or more property key-value pairs. It returns a JSON object that contains an object member for each of those key-value pairs.

JSON_ARRAYAGG:-The SQL/JSON function JSON_ARRAYAGG is an aggregate function. It takes as its input a column of SQL expressions, converts each expression to a JSON value, and returns a single JSON array that contains those JSON values.

JSON_QUERY:-JSON_QUERY finds one or more specified JSON values in JSON data and returns the values in a character string.

The two ways in which can perfrom schema validations of JSON data.

• While creating the tables in which the generated json will be inserted , create a constraints on the column as shown below .
• While selecting the generated json data , use where condition like ‘column_name IS JSON’, this will select only the valid JSON dat and will not select null.

Example

CREATE TABLE tmp_json_gen (json_data CLOB

---constraints to check if the generated JSON data is in proper format or not--
CONSTRAINT ensure_json CHECK (json_data IS JSON));
CREATE TABLE tmp_json_gen_pretty (json_data CLOB
CONSTRAINT ensure_json_pty CHECK (json_data IS JSON));

Example

DECLARE
  l_clob  CLOB;

BEGIN
FOR CUR IN (SELECT customer_id FROM customers)
 LOOP
SELECT /*json*/
 JSON_OBJECT('id' VALUE c.customer_id,
             'name' VALUE c.full_name,
             'num_orders' VALUE (SELECT COUNT(*)
                FROM orders o
               WHERE o.customer_id = c.customer_id),
             'orders' VALUE
             (SELECT JSON_ARRAYAGG(JSON_OBJECT('order_id' VALUE o.order_id,
                                               'date' VALUE o.order_datetime,
                                               'items' VALUE
                                               (SELECT JSON_ARRAYAGG(JSON_OBJECT
                                            ('id'
                                                    VALUE
                                                      i.order_id,
                                                       'name'
                                                        VALUE
                                                        i.line_item_id,
                                                         'quantity'
                                                         VALUE
                                                        i.quantity,
                                                        'price'
                                                         VALUE
                                                         i.unit_price,
                                                        'total_price'
                                                        VALUE(i.unit_price *
                                                        i.quantity)))
                                                  FROM order_items i
                                                 WHERE i.order_id = o.order_id  )    
                                             )
                                   )
                FROM orders o
               WHERE o.customer_id = c.customer_id) ABSENT ON NULL)
  INTO l_clob
  FROM customers c
 WHERE customer_id = '' || CUR.customer_id || '';

Example

INSERT INTO tmp_json_gen VALUES(l_clob);
COMMIT;
END LOOP;

INSERT INTO tmp_json_gen_pretty
WITH tmp AS
   (SELECT JSON_QUERY(json_data, '$' pretty) AS json_data FROM tmp_json_gen)
SELECT * FROM tmp WHERE json_data IS JSON;

COMMIT;
END;

Output

{
    "id" : 21,
    "name" : "Martha Baker",
    "num_orders" : 4,
    "orders" :
    [
      {
        "order_id" : 1775,
        "date" : "2019-03-03T18:44:22.601072",
        "items" :
        [
          {
            "id" : 1775,
            "name" : 1,
            "quantity" : 2,
            "price" : 29.51,
            "total_price" : 59.02
          },
          {
            "id" : 1775,
            "name" : 2,
            "quantity" : 4,
            "price" : 44.17,
            "total_price" : 176.68
          }
        ]
      },
      {
        "order_id" : 1807,
        "date" : "2019-03-09T09:16:47.441189",
        "items" :
        [
          {
            "id" : 1807,
            "name" : 1,
            "quantity" : 3,
            "price" : 48.39,
            "total_price" : 145.17
          },
          {
            "id" : 1807,
            "name" : 2,
            "quantity" : 2,
            "price" : 38.28,
            "total_price" : 76.56
          }
        ]
      },
      {
        "order_id" : 1824,
        "date" : "2019-03-12T23:56:53.384122",
        "items" :
        [
          {
            "id" : 1824,
            "name" : 1,
            "quantity" : 2,
            "price" : 11,
            "total_price" : 22
          },
          {
            "id" : 1824,
            "name" : 2,
            "quantity" : 3,
            "price" : 10.48,
            "total_price" : 31.44
          },
          {
            "id" : 1824,
            "name" : 3,
            "quantity" : 3,
            "price" : 43.71,
            "total_price" : 131.13
          }
        ]
      },
      {
        "order_id" : 1134,
        "date" : "2018-11-18T07:46:53.922156",
        "items" :
        [
          {
            "id" : 1134,
            "name" : 1,
            "quantity" : 3,
            "price" : 48.39,
            "total_price" : 145.17
          },
          {
            "id" : 1134,
            "name" : 2,
            "quantity" : 4,
            "price" : 49.12,
            "total_price" : 196.48
          }
        ]
      }
    ]
  }