如何在 Oracle 中生成 JSON 数据并执行模式验证?
问题:
您想要在 Oracle 中生成 JSON 数据并执行模式验证。
解决方案
可以使用 JSON_OBJECT、JSON_ARRAYAGG、JSON_QUERY 等函数通过多列和多表生成复杂的 JSON 数据。
JSON_OBJECT:- 它是一个 SQL/JSON 函数。JSON_OBJECT 将一个或多个属性键值对作为输入。它返回一个 JSON 对象，该对象包含每个键值对的对象成员。
JSON_ARRAYAGG:-SQL/JSON 函数 JSON_ARRAYAGG 是一个聚合函数。它将一列 SQL 表达式作为输入，将每个表达式转换为 JSON 值，并返回包含这些 JSON 值的单个 JSON 数组。
JSON_QUERY:-JSON_QUERY 在 JSON 数据中查找一个或多个指定的 JSON 值并以字符串形式返回这些值。
两种方法可以执行 JSON 数据的架构验证。
- 在创建将插入生成的 json 的表时，请对列创建约束，如下所示。
- 在选择生成的 json 数据时，使用 where 条件，如"column_name IS JSON"，这将仅选择有效的 JSON 数据，而不会选择 null。
示例
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));
示例
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 || '';
示例
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;
输出
{ "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 } ] } ] }
