如何在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_name IS JSON”，这将仅选择有效的JSON dat，而不会选择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
          }
        ]
      }
    ]
  }