数据结构
网络
关系数据库管理系统 (RDBMS)
操作系统
Java
MS Excel
iOS
HTML
CSS
Android
Python
C语言编程
C++
C#
MongoDB
MySQL
Javascript
PHP
物理学
化学
生物学
数学
英语
经济学
心理学
社会学
服装学
法学如何在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数据时，使用类似“column_name IS JSON”的where条件，这将只选择有效的JSON数据，而不会选择空值。
示例
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 } ] } ] }
882 次浏览
