Hi, I have relation 'resources' in database with 2 fields, 'id' and 'data'. 'id' eg. 25 and 'data' eg.

{"href":null,"id":"25","publicIdentifier":null,"description":null,"category":null,"validFor":null,"name":null,"lifecycleState":null,"type":"Resource","baseType":null,"schemaLocation":null,"version":null,"resourceRelationship":[{"type":"requires","validFor":null,"resourceRef":{"id":"46","href":" http://server:port/resourceInventoryManagement/logicalResource/46"},"resourceRelationshipCharacteristic":[{"name":"priority","value":"2","schemaLocation":null,"type":null},{"name":"accuracy","value":{"unit":"second","amount":"5","@type":"accuracy"},"schemaLocation":null,"type":null}]}],"place":null,"note":[],"resourceSpecification":null,"relatedParty":[{"href":"http://serverlocation:port/PartyManagement/individual/43","id":"44","name":null,"role":"Manufacturer","validFor":null}],"resourceCharacteristic":[],"resourceAttachment":[]}

I have to select some fields from 'data' eg. id and relatedParty and build json from this fields and write as a CLOB/VARCHAR. In version 12.2 or highest this select could be look (I think) ->

SELECT JSON_OBJECT('id' VALUE JSON_VALUE(data, '$.id'), 'relatedParty' VALUE JSON_QUERY(data, '$.relatedParty')) from resources where JSON_VALUE(data, '$.id') = '25';

but I can't use highest version so I look for solutions for my problem. I read that JSON_OBJECT function can be work only 12.2 version or highest. Thanks for all answers and sorry for my English but I think my problem is clear to understand.

POST send to database have a body ->

{
  "id" : "25",
  "type" : "Resource",
  "relatedParty" : [ {
    "id" : "44",
    "href" : "http://serverlocation:port/PartyManagement/individual/43",
    "role" : "Manufacturer",
    "name" : null,
    "validFor" : null
  } ],

  "resourceRelationship" : [ {
    "type" : "requires",
    "validFor" : null,
    "resourceRef" : {
      "id" : "46",
      "href" : " http://server:port/resourceInventoryManagement/logicalResource/46"
    },
    "resourceRelationshipCharacteristic" : [ {
      "name" : "priority",
      "value" : "2",
      "@schemaLocation" : "//http:server:port//resourceInventoryManagement/schema/accurancy.yml",
      "@type" : null
    }, {
      "name" : "accuracy",
      "value" : {
        "unit" : "second",
        "amount" : "5",
        "@type" : "accuracy"
      },
      "@schemaLocation" : "//http:server:port//resourceInventoryManagement/schema/accurancy.yml",
      "@type" : null
    } ]
  } ]


}

create table t (
  jdata varchar2(4000) check ( jdata is json )
);
insert into t values ('{
  "href": null,
  "id": "25",
  "publicIdentifier": null,
  "description": null,
  "category": null,
  "validFor": null,
  "name": null,
  "lifecycleState": null,
  "type": "Resource",
  "baseType": null,
  "schemaLocation": null,
  "version": null,
  "resourceRelationship": [
    {
      "type": "requires",
      "validFor": null,
      "resourceRef": {
        "id": "46",
        "href": "http://server:port/resourceInventoryManagement/logicalResource/46"
      },
      "resourceRelationshipCharacteristic": [
        {
          "name": "priority",
          "value": "2",
          "schemaLocation": null,
          "type": null
        },
        {
          "name": "accuracy",
          "value": {
            "unit": "second",
            "amount": "5",
            "@type": "accuracy"
          },
          "schemaLocation": null,
          "type": null
        }
      ]
    }
  ],
  "place": null,
  "note": [],
  "resourceSpecification": null,
  "relatedParty": [
    {
      "href": "http://serverlocation:port/PartyManagement/individual/43",
      "id": "44",
      "name": null,
      "role": "Manufacturer",
      "validFor": null
    }
  ],
  "resourceCharacteristic": [],
  "resourceAttachment": []
}');

commit;

declare
  cur sys_refcursor;
begin

  open cur for 
    select json_query ( 
             jdata,'$.relatedParty' 
           ) "relatedParty"
    from   t;
  apex_json.initialize_clob_output;

  apex_json.open_object;
  apex_json.write('relatedParty', cur);
  apex_json.close_object;

  dbms_output.put_line(apex_json.get_clob_output);
  apex_json.free_output;
end;
/

{
"relatedParty":[
{
"relatedParty":"[{\"href\":\"http:\/\/serverlocation:port\/PartyManagement\/individual\/43\",\"id\":\"44\",\"name\":null,\"role\":\"Manufacturer\",\"validFor\":null}]"
}
]
}