如何在Oracle18c中创建手动JSON？

Question

我需要创建手动json来将其作为输入发送到REST API。在过去的12c(v12.0.1.2)中，我使用Oracle，在做了一些研究后发现APEX_JSON 18c

Object Types in JSON

我不能使用SQL/PL_SQL函数，因为我需要创建手动json。那么，有人能建议APEX_JSON在哪里更好吗?或者在性能和解析方面，拥有JSON_OBJECT_T、JSON_ARRAY_T、etc的新API更好？

这是我需要创建的样例JSON。在这个JSON中，只有routeStops数组可以从数据库中访问，并且将根据记录的数量有多个停靠点，但除此之外，其他值在整个json中都是单一的，并且需要硬编码值？因此，现在请建议我是否可以使用SQL函数来实现这一点？

"routeProfile": {
        "resourceProfileRef": "7T5FRANBSC",
        "driverRef": "",
        "vehicleRef": "",
        "dutyStartTime": "10:30",
        "dutyDurationHours": 0,
        "startLocation": {
            "knownLocationRef": "",
            "houseName": "",
            "address1": "",
            "address2": "",
            "address3": "",
            "address4": "",
            "postCode": "",
            "countryCode": "",
            "location": {
                "coordinates": [-999,
                    -999],
                "type": "Point"
            }
        },
        "mandatoryFirstStop": false,
        "mandatoryFirstStopLocation": {
            "knownLocationRef": "",
            "houseName": "",
            "address1": "",
            "address2": "",
            "address3": "",
            "address4": "",
            "postCode": "",
            "countryCode": "",
            "location": {
                "coordinates": [-999,
                    -999],
                "type": "Point"
            }
        },
        "mandatoryFirstStopTime": 0,
        "mandatoryLastStop": false,
        "mandatoryLastStopLocation": {
            "knownLocationRef": "",
            "houseName": "",
            "address1": "",
            "address2": "",
            "address3": "",
            "address4": "",
            "postCode": "",
            "countryCode": "",
            "location": {
                "coordinates": [-999,
                    -999],
                "type": "Point"
            }
        },
        "mandatoryLastStopTime": 0,
        "endLocation": {
            "knownLocationRef": "",
            "houseName": "",
            "address1": "",
            "address2": "",
            "address3": "",
            "address4": "",
            "postCode": "",
            "countryCode": "",
            "countryCode": "",
            "location": {
                "coordinates": [-999,
                    -999],
                "type": "Point"
            }
        }
    },
    "routeStops": [{
            "stop": 1,
            "location": {
                "knownLocationRef": "",
                "houseNumber": "",
                "houseName": "Shop XYZ",
                "address1": "Ruddington Lane",
                "address2": "Wilford",
                "address3": "Nottingham",
                "address4": "",
                "postCode": "NG11 7DQ",
                "countryCode": "GB",
                "location": {
                    "coordinates": [-999,
                        -999],
                    "type": "Point"
                }
            },
            "jobs": [{
                    "jobRef": "3735081",
                    "jobTypeRef": "STDSTOPJOB",
                    "customer": {
                        "title": "",
                        "initials": "",
                        "firstName": "",
                        "lastName": "",
                        "homePhone": "",
                        "workPhone": "",
                        "mobilePhone": "",
                        "email": ""
                    },
                    "location": {
                        "knownLocationRef": "",
                        "houseNumber": "",
                        "houseName": "Shop XYZ",
                        "address1": "Ruddington Lane",
                        "address2": "Wilford",
                        "address3": "Nottingham",
                        "address4": "",
                        "postCode": "NG11 7DQ",
                        "countryCode": "GB",
                        "location": {
                            "coordinates": [-999,
                                -999],
                            "type": "Point"
                        }
                    },
                    "customerAccountRef": "CUSTACC001",
                    "jobScheduling": {
                        "schedulingDateTimeEarliest": "2018-12-21 00:00",
                        "schedulingDateTimeLatest": "2018-12-21 23:59",
                        "excludeDateTimeEarliest": "2018-12-21 12:00",
                        "excludeDateTimeLatest": "2018-12-21 13:00"
                    }
                }]
            }]

Answer 1

如果使用SQL select获取数据，则可以使用12.2中添加的JSON生成函数。它们是：

• JSON_object
• JSON_objectagg
• JSON_array
• JSON_arrayagg

例如，下面的代码使用标准HR架构按部门创建一个employee对象数组：

select json_object (
         'department' value d.department_name,
         'employees' value json_arrayagg (
           json_object (
             'name' value first_name || ', ' || last_name, 
             'job' value job_title
       ))) DOC
from   hr.departments d, hr.employees e, hr.jobs j
where  d.department_id = e.department_id
and    e.job_id = j.job_id
and    d.department_id = 110
group  by d.department_name;

DOC   
{
  "department" : "Accounting",
  "employees" :
  [
    {
      "name" : "Shelley, Higgins",
      "job" : "Accounting Manager"
    },
    {
      "name" : "William, Gietz",
      "job" : "Public Accountant"
    }
  ]
}   

您可以在JSON Developer's Guide中找到有关这些内容的更多信息