# Update to response 02/10/2022 UTC-05:00 Colombia
Hi,

I am happy to report that I have achieved my goal of outputting my REST service and achieved it with the following documentation
https://oracle-base.com/articles/misc/apex_json-package-generate-and-parse-json-documents-in- oracle
I only have one doubt with the package, and it is that if there are no values, I return null instead of an empty array {}
Ex:

   {
                            "name": "name",
                            "label": "Ingrese nombre",
                            "type": "text",
                            "options": null,
                            "validators": [
                                {
                                    "required": true
                                }
                            ]
                   },

In the same way, my teammates love the result.

I have a suggestion regarding APEX with JSON and I think when creating a rest service using the JSON_OBJECT() property APEX doesn't lose the JSON format but returns it as a JSON and ideally it would work without an apex package anymore that this native form of Oracle SQL is easier for me than the documentation that I commented above.

This was the documentation I used:
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/adjsn/generation.html#GUID-1084A518-A44A-4654-A796-C1DD4D8EC2AA

I would like to know if in the future it would be possible to implement that option in the rest services when using JSON_OBJECT.


Hi,

I am using the Oracle JSON Developer's Guide documentation and I have problems with the results using json_object since using this function it returns a string instead of an array and when I intensely use JSON.parse() in javascript it cannot convert it because the arrays do not separate them by commas, this is my query:

select
    syw_fields2.subform,
    JSON_ARRAY( listagg(    
    json_object(
    'name' value syw_fields2.name,
    'label' value syw_fields2.label,
    'value' value syw_fields2.value,
    'type' value syw_fields2.type,
    'validators' value json_object(
    'required' value 'true',
    'minlength' value '10'
    )
format json))) as column_data
from syw_fields syw_fields2
where syw_fields2.form_id = (select sywforms.form_id from syw_forms sywforms where form_identity = :id)
group by syw_fields2.subform;

Output:

{
    "items": [
        {
            "subform": "formname",
            "column_data": "[{\"name\":\"name\",\"label\":\"Your name\",\"value\":null,\"type\":\"text\",\"validators\":{\"required\":\"true\",\"minLength\":\"10\"}}{\"name\":\"lastname\",\"label\":\"Ingrese apellido\",\"value\":null,\"type\":\"text\",\"validators\":{\"required\":\"true\",\"minLength\":\"10\"}}]"
        }
    ],
    "hasMore": false,
    "limit": 10,
    "offset": 0,
    "count": 1,
    "links": [
        {
            "rel": "self",
            "href": ""
        },
        {
            "rel": "describedby",
            "href": ""
        },
        {
            "rel": "first",
            "href": ""
        }
    ]
}

An error I get when starting a new array is that there is no comma separator:

"column_data": "[{\"name\":\"name\",\"label\":\"Your name\",\"value\":null,\"type\":\"text\",\"validators\":{\"required\":\"true\",\"minLength\":\"10\"}}THERE IS NOT(,){\"name\":\"lastname\",\"label\":\"Ingrese apellido\",\"value\":null,\"type\":\"text\",\"validators\":{\"required\":\"true\",\"minLength\":\"10\"}}]"


If there is no comma, the conversion to JSON fails.