如何在Oracle中生成JSON数据并执行模式验证？
问题：
您要生成JSON数据并在Oracle中执行模式验证。
解
诸如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_nameISJSON”，这将仅选择有效的JSONdat，而不会选择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
}
]
}
]
}