How to 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           }         ]       }     ]   }

Updated on: 05-Dec-2020

732 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements