如何在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

          }

        ]

      }

    ]

  }

以上是 如何在Oracle中生成JSON数据并执行模式验证? 的全部内容, 来源链接: utcz.com/z/334686.html

回到顶部