How to create manual JSON in Oracle 18c?

Question

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

Object Types in JSON

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?

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"
                    }
                }]
            }]

Answer 1

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

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"
    }
  ]
}   

You can find out more about these in the JSON Developer's Guide