如何在Oracle 18c中创建手动JSON? [英] How to create manual JSON in Oracle 18c?
问题描述
我需要创建手动json以将其作为输入发送到REST API.过去在12c(v12.0.1.2)中，我使用的是APEX_JSON API，经过一些研究后发现Oracle 18c具有
I need to create manual json to send it as an input to a REST API. In past in 12c(v12.0.1.2), I was using APEX_JSON API and after doing some research found that Oracle 18c has
我无法使用SQL/PL_SQL函数，因为我需要创建手动json.因此，有人可以在性能和解析方面建议APEX_JSON更好的地方，还是具有JSON_OBJECT_T，JSON_ARRAY_T，etc的新API更好?
I can't use SQL/PL_SQL functions as I need to create manual json. So can someone suggest where APEX_JSON is better or new API having JSON_OBJECT_T, JSON_ARRAY_T, etc are better in terms of performance and parsing?
这是我需要创建的示例JSON.在此JSON中，只能从数据库访问routeStops数组，并且该数组将基于记录数而有多个停靠点，但是除了其他值，整个json中的其他值都是单个的并且需要是硬编码的值吗?因此，现在请建议我可以使用SQL函数来实现这一目标吗?
Here is my sample JSON which I need to create. In this JSON only routeStops array can be accessed from Database and will have multiple stops based on the number of records but other than that other values are single in entire json and need to be hard coded values? So now please suggest can I achieve this with SQL functions?
"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 selects获取数据，则可以使用12.2中添加的JSON生成函数.这些是:
If you're getting the data using SQL selects, you can use the JSON generation functions added in 12.2. These are:
- JSON_object
- JSON_objectagg
- JSON_array
- JSON_arrayagg
例如，以下使用标准的HR模式按部门创建一组雇员对象:
For example, the following creates an array of employee objects by department, using the standard HR schema:
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"
}
]
}
您可以在 查看全文
