json - 如何在 Oracle 18c 中创建手动 JSON?
问题描述
我需要创建手动 json 以将其作为输入发送到 REST API。在过去的 12c(v12.0.1.2) 中,我使用的是 APEX_JSON API,经过一些研究发现 Oracle 18c 具有
我不能使用 SQL/PL_SQL 函数,因为我需要创建手动 json。那么有人可以建议 APEX_JSON 在哪里更好,或者新的 APIJSON_OBJECT_T
在性能和解析方面更好吗JSON_ARRAY_T
?etc
这是我需要创建的示例 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"
}
}]
}]
解决方案
如果您使用 SQL 选择获取数据,则可以使用 12.2 中添加的 JSON 生成函数。这些是:
- JSON_object
- JSON_objectagg
- JSON_array
- JSON_arrayagg
例如,以下使用标准 HR 模式按部门创建一个员工对象数组:
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 开发人员指南中找到有关这些的更多信息
推荐阅读
- web-crawler - 使用 Stormcrawler 爬取特定子目录
- javascript - D3.js 惯性拖动
- swift - 为什么在用于 OpenGL 纹理的 CGContext 上调用绘图函数时出现 BAD_ACCESS 错误?
- javascript - JavaScript 窗口卸载不工作 [Vanilla Javascript]
- android - 使用 WireMockRule 运行 Android UI 测试时出现 java.lang.NoClassDefFoundError
- javascript - 如何在更新表单时发送有限字段
- api - XSLT 1.0 将元素添加到多个匹配项
- oauth-2.0 - 带有 oauth2-google 的 PHPMailer 是否使用 get_oauth_token
- android - cardview的recycleview不起作用。我该如何解决?
- javascript - 如何获取用户输入的访问权限并在我的后端服务器上计算某些内容?