生成 JSON 数据

通过使用 SQL 函数，可以从关系表生成格式化的 JSON 数据。

我们将使用关系数据来生成 JSON。我们将从两个表中的数据开始。其中一个包含基本员工数据。另一个包含帐户信息。它们将按此处所示进行创建和填充。


create table empdata (id int,
                      last_name varchar(10),
                      first_name varchar(10), 
                      office_number varchar(10),
                      work_phone varchar(20),
                      home_phone varchar(20));
 
create table emp_account (id int,
                          account varchar(20));                      
                      
insert into empdata values (901, 'Doe', 'John', 'E-334', '555-7242', '555-3762');
insert into emp_account values (901, '36232'), (901, '73263');

insert into empdata values (902, 'Pan', 'Peter', 'E-216', '555-8925', null);
insert into emp_account values (902, '76232'), (902, '72963');

insert into empdata values (903, 'Jones', 'Mary', 'E-739', '555-4311', '555-6312');

insert into empdata values (904, 'Smith', 'Sally', null, null, null);

我们的目标是在 JSON 中生成四条员工信息，这些信息类似于用作 JSON_TABLE 示例输入的以下 JSON 对象。



{"id":901, "name" : { "first":"John", "last":"Doe" }, "office" : "E-334",   
      "phones" : [ { "type":"home", "number":"555-3762" }, 
                   { "type":"work", "number":"555-7242" } ],      
      "accounts" : [ { "number":"36232"}, { "number":"73263"}] } 

{"id":902, "name" : { "first":"Peter", "last":"Pan" }, "office" : "E-216", 
      "phones" : [ { "type":"work", "number":"555-8925" } ], 
      "accounts" : [ { "number":"76232"}, {"number":"72963"}] }

{"id":903, "name" : { "first":"Mary", "last":"Jones" }, "office" : "E-739", 
      "phones" : [ { "type":"home", "number":"555-6312" }, 
                   { "type":"work", "number":"555-4311" } ], }

{"id":904, "name" : { "first":"Sally", "last":"Smith" } }

构建基本 JSON 对象

让我们从一个简单的示例开始，了解生成基本 JSON 对象的简单程度。

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number) 
from empdata;

此示例使用 JSON_OBJECT 标量函数来生成 JSON 对象。它使用 EMPDATA 表中的标识， LAST_NAME 和 OFFICE_NUMBER 列来为值定义三个 key: value 对。每个键名都指定为一个字符串，就像它将出现在输出中一样。此查询的结果是四行，每行对应 EMPDATA 表中的一行。

{"id":901,"name":"Doe","office":"E-334"}
{"id":902,"name":"Pan","office":"E-216"}
{"id":903,"name":"Jones","office":"E-739"}
{"id":904,"name":"Smith","office":null}

这是实现最终目标的良好开端，但我们可能希望在 office 值为空时省略该值，而不是将其包括在内。这很容易通过使用 ABSENT ON NULL 子句来实现。这指示当 JSON 对象的任何值为空值时，结果中不应包含空 key: value 对。

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number
                    absent on null) 
from empdata;

现在没有 904 的 office 值。

{"id":901,"name":"Doe","office":"E-334"}
{"id":902,"name":"Pan","office":"E-216"}
{"id":903,"name":"Jones","office":"E-739"}
{"id":904,"name":"Smith"}

嵌套 JSON 对象

接下来，我们将致力于嵌入复合名称对象。首先，让我们自行生成。

select json_object ('first' value first_name, 
                    'last' value last_name) 
from empdata;

此查询的结果为:

{"first":"John","last":"Doe"}
{"first":"Peter","last":"Pan"}
{"first":"Mary","last":"Jones"}
{"first":"Sally","last":"Smith"}

由于这看起来是正确的，因此我们会将其包含在主查询中。我们需要做的就是将此 name JSON 对象嵌入到外部对象中。

select json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number  
                    absent on null) 
from empdata;

现在，我们的结果如下所示，在外部 JSON 对象中嵌套了 name 对象:

{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334"}
{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216"}
{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739"}
{"id":904,"name":{"first":"Sally","last":"Smith"}}

构建 JSON 数组

接下来我们将处理电话阵列。这需要是 JSON 数组，而不是 JSON 对象。这意味着我们将使用 JSON_ARRAY 标量函数。为避免为未提供的电话号码生成数组条目，将使用 CASE 语句。如果两个电话号码都不存在，那么将生成空的 JSON 数组。由于我们知道是生成家庭电话号码还是工作电话号码，因此我们将 type 值指定为 home 和 work。

select json_array 
        (case when home_phone is not null then 
            json_object('type' value 'home', 
                        'number' value home_phone) end, 
         case when work_phone is not null then 
            json_object('type' value 'work', 
                        'number' value work_phone) end 
         )
from empdata;

这是结果。每个条目的输出分为两行以显示完整结果。

["{\"type\":\"home\",\"number\":\"555-3762\"}",
    "{\"type\":\"work\",\"number\":\"555-7242\"}"]

["{\"type\":\"work\",\"number\":\"555-8925\"}"]

["{\"type\":\"home\",\"number\":\"555-6312\"}",
    "{\"type\":\"work\",\"number\":\"555-4311\"}"]

[]

这有点出乎意料。为什么结果中都是 \ 字符？这演示了处理正常字符数据与已格式化为 JSON 的字符数据之间的差异。当 JSON_ARRAY (或任何 JSON 发布函数) 正在查看其自变量时，它会识别该自变量何时是另一个 JSON 函数的直接结果。如果是，那么会将该字符串解释为已格式化的 JSON 数据。这意味着函数不会对字符串中的任何特殊字符进行转义。如果自变量不是 JSON 函数的直接结果，那么会将其解释为非 JSON 字符数据，并对该值执行转义处理。由于此示例在 CASE 表达式中嵌入了 JSON_OBJECT ，因此字符串已格式化为 JSON 的事实未知。为了避免这些不需要的转义序列，您需要使用 FORMAT JSON 子句显式告知 JSON_ARRAY 参数已是 JSON。

有了这些知识，我们再试一试。

select json_array 
  (case when home_phone is not null then 
      json_object('type' value 'home', 
                  'number' value home_phone) end 
      format json,
   case when work_phone is not null then 
      json_object('type' value 'work', 
                  'number' value work_phone) end 
      format json)  
from empdata;

现在的结果是:

[{"type":"home","number":"555-3762"},{"type":"work","number":"555-7242"}]
[{"type":"work","number":"555-8925"}]
[{"type":"home","number":"555-6312"},{"type":"work","number":"555-4311"}]
[]

现在，这些信息就可以作为 JSON 对象的下一部分了。

select json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number, 
                    'phones' value json_array 
                                 (case when home_phone is not null then 
                                     json_object('type' value 'home', 
                                                 'number' value home_phone 
                                                 ) end format json,
                                  case when work_phone is not null then 
                                     json_object('type' value 'work', 
                                                  'number' value work_phone 
                                                  ) end format json)
                     absent on null) 
from empdata;

这将返回:

{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334",
     "phones":[{"type":"home","number":"555-3762"},{"type":"work","number":"555-7242"}]}

{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216",
     "phones":[{"type":"work","number":"555-8925"}]}

{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739",
     "phones":[{"type":"home","number":"555-6312"},{"type":"work","number":"555-4311"}]}

{"id":904,"name":{"first":"Sally","last":"Smith"},
     "phones":[]}

从多行数据构建数组

我们几乎完成了。我们需要提取存储在单独表中的帐户信息，并将其放入数组中。让我们生成一个包含帐号的对象数组。

select json_array(json_object('number' value account)) 
from emp_account;

此查询的结果为:

[{"number":"36232"}]
[{"number":"73263"}]
[{"number":"76232"}]
[{"number":"72963"}]

这将为每个帐号生成一个数组。对于每个 ID 值，我们需要一个数组。这需要将单个 ID 值的所有 number JSON 对象聚集到单个数组中。

select json_arrayagg(json_object('number' value account)) 
from emp_account group by id;

JSON_ARRAYAGG 是在数据组上工作的聚集函数。在这种情况下，我们将对 ID 列进行分组。 ID 的每个帐户都会生成一个对象，然后将所有这些对象聚集到单个数组中。此查询仅返回两行，每行对应一个 ID 值，这正是我们要查找的内容。

[{"number":"36232"},{"number":"73263"}]
[{"number":"76232"},{"number":"72963"}]

这一块可以添加到我们到目前为止完成生成的 JSON 对象的内容中。要仅返回 EMP_ACCOUNT 表中当前标识的帐户，需要 WHERE 子句。另请注意，由于将从查询返回帐户数组，因此需要添加 FORMAT JSON 子句，以便外部 JSON_OBJECT 将该值正确标识为已格式化的 JSON。

select json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number, 
                    'phones' value json_array 
                                 (case when home_phone is not null then 
                                     json_object('type' value 'home', 
                                                 'number' value home_phone 
                                                 ) end format json,
                                  case when work_phone is not null then 
                                     json_object('type' value 'work', 
                                                  'number' value work_phone 
                                                  ) end format json),
                    'accounts' value (select json_arrayagg(
                                                json_object('number' value account)) 
                                      from emp_account a 
                                      where a.id = e.id group by id) format json
                    absent on null) 
from empdata e;

现在我们已经得出了最后的结果。

{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334",
      "phones":[{"type":"home","number":"555-3762"},
                {"type":"work","number":"555-7242"}],
      "accounts":[{"number":"36232"},{"number":"73263"}]}

{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216",
      "phones":[{"type":"work","number":"555-8925"}],
      "accounts":[{"number":"76232"},{"number":"72963"}]}

{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739",
      "phones":[{"type":"home","number":"555-6312"},
                {"type":"work","number":"555-4311"}]}

{"id":904,"name":{"first":"Sally","last":"Smith"},"phones":[]}

将 JSON 组合到一个对象中

让我们更进一步。有时，您需要包含所有信息的单个 JSON 文档。我们将把个人的结果，捆绑在一起。


select json_object('employees' value json_arrayagg(

       json_object ('id' value id, 
                    'name' value json_object ( 'first' value first_name, 
                                               'last' value last_name), 
                    'office' value office_number, 
                    'phones' value json_array 
                                 (case when home_phone is not null then 
                                     json_object('type' value 'home', 
                                                 'number' value home_phone 
                                                 ) end format json,
                                  case when work_phone is not null then 
                                     json_object('type' value 'work', 
                                                  'number' value work_phone 
                                                  ) end format json),
                    'accounts' value (select json_arrayagg(
                                                json_object('number' value account)) 
                                      from emp_account a 
                                      where a.id = e.id group by id) format json
                    absent on null))) 
from empdata e;

首先，我们使用 JSON_ARRAYAGG 来打包结果，以创建员工信息数组。然后，我们使用 JSON_OBJECT 将数组打包，因此最终结果是单个 JSON 对象。现在，结果是一个 JSON 对象。

{"employees":[
{"id":901,"name":{"first":"John","last":"Doe"},"office":"E-334",
      "phones":[{"type":"home","number":"555-3762"},
                {"type":"work","number":"555-7242"}],
      "accounts":[{"number":"36232"},{"number":"73263"}]},
{"id":902,"name":{"first":"Peter","last":"Pan"},"office":"E-216",
      "phones":[{"type":"work","number":"555-8925"}],
      "accounts":[{"number":"76232"},{"number":"72963"}]},
{"id":903,"name":{"first":"Mary","last":"Jones"},"office":"E-739",
      "phones":[{"type":"home","number":"555-6312"},
                {"type":"work","number":"555-4311"}]},
{"id":904,"name":{"first":"Sally","last":"Smith"},"phones":[]}
]}

设置结果数据类型

每个发布函数都允许您指定函数结果的数据类型和格式。如果省略正在返回的子句，那么结果将是长度为 2G 且 CCSID 为 1208 的 CLOB。它将格式化为 FORMAT JSON。如果需要其他结果 (例如， VARBINARY (2000) 列中的 BSON) ，请按如下所示添加正在返回的子句:

select json_object ('id' value id, 
                    'name' value last_name, 
                    'office' value office_number
                    returning varbinary(2000) format bson) 
from empdata;

使用公共表表达式来聚集不同值

假设我们要汇总每个销售人员的销售信息，列出在其中进行销售的每个区域。在 SALES 表中，每个销售都有一个条目。我们需要一个唯一销售人员的列表，以及每个销售人员所在区域的列表。这些是使用每个表的公共表表达式生成的。这些临时结果用于生成 JSON 对象，每个销售人员一个对象。

with sales_tmp(sales_person) as (
    select distinct(sales_person) from sales),
  region_tmp(region,sales_person) as ( 
    select distinct region, sales_person from sales)

  select json_object( key sales_person 
                      value (select JSON_ARRAYAGG(region order by region) 
                             from region_tmp r where r.sales_person = s.sales_person) 
                      format json)
FROM sales_tmp s order by sales_person;

从样本表中，将返回以下三行:

{"GOUNOT":["Manitoba","Ontario-North","Ontario-South","Quebec"]}
{"LEE":["Manitoba","Ontario-North","Ontario-South","Quebec"]}
{"LUCCHESSI":["Manitoba","Ontario-South","Quebec"]}

如果要返回包含所有结果的单个 JSON 对象，那么使用 JSON_OBJECTAGG 的以下查询将为您生成该对象。

with sales_tmp(sales_person) as (
    select distinct(sales_person) from sales),
  region_tmp(region,sales_person) as ( 
    select distinct region, sales_person from sales)

  select json_objectagg( key sales_person 
                         value (select JSON_ARRAYAGG(region order by region) 
                               from region_tmp r where r.sales_person = s.sales_person) 
                         format json)
FROM sales_tmp s;

这将返回一行，其中包含一个 JSON 对象。这里分为几行，便于阅读。

{"LEE":["Manitoba","Ontario-North","Ontario-South","Quebec"],
"GOUNOT":["Manitoba","Ontario-North","Ontario-South","Quebec"],
"LUCCHESSI":["Manitoba","Ontario-South","Quebec"]}