Can JSON_VALUE return multiple values

The answer to your question depends upon how the JSON documents are structured. But first of all, yes, you can obtain any JSON values from the data types VARCHAR2, BLOB and CLOB.

I assume, based on how you explained the requirement, the structure of the JSON will come with an array of regions which in themselves are objects as you pointed out above. So let's say you have a JSON like this:

{
   "REGIONS": [
      {"REGION": "AMR", "CNTRY": "US"},
      {"REGION": "EUR", "CNTRY": "AT"}
   ]
}

In such a case JSON_VALUE is not sufficient anymore as JSON_VALUE is designed to obtain a given scalar value from a JSON document (note that Oracle 12.2 will have significant enhancements in that area). But in the case above you actually need to model that array of objects into relational rows, i.e. (you have one JSON document but an array of entries that should become a row each). In such a case you have to use JSON_TABLE to generate the rows. So for the example above you can write a query such as:

CREATE TABLE customers (doc CLOB CONSTRAINT doc_valid_json CHECK (doc IS JSON));
INSERT INTO customers (doc) VALUES ('
{
   "REGIONS": [
      {"REGION": "AMR", "CNTRY": "US"},
      {"REGION": "EUR", "CNTRY": "AT"}
   ]
}');

COMMIT;

SELECT jt.region, jt.cntry
  FROM customers c, JSON_TABLE(c.doc, '$.REGIONS[*]'
                                  COLUMNS (region VARCHAR2(3) PATH '$.REGION',
                                           cntry  VARCHAR2(2) PATH '$.CNTRY')) jt;

The SQL above will do the following, starting at the FROM clause: It reads the table customers which holds our JSON documents the column doc as a CLOB. Next it applies the JSON_TABLE operator which does a couple of tricks for us. First we need to pass it the column containing the JSON document, in our case the doc column. Then we can give it a path prefix, i.e. a mechanism so that we don't have to repeat the whole path over and over again. We tell JSON_TABLE that we only want to look at all the elements that comes in the array for "REGIONS", mark via [*]. As the last step we have to tell JSON_TABLE what we want those elements look like in the relational world, i.e. what column names and data types we want to use. This is done via the COLUMNS key word. we specify that we would like to have column region and cntry, both of type VARCHAR2 and the path within the JSON document. As we already told JSON_TABLE that we only want to look at the array elements of REGIONS via '$.REGIONS[*]' we can now just go and pick the elements of the array, in this case $.REGION and $.CNTRY. Next steps are simple. First we define a table alias for JSON_TABLE, in this case jt. Now going back to the SELECT part we can just select those two columns that we have just generated out of the JSON document, SELECT jt.region, jt.cntry.

You can have a look and try the above example in LiveSQL.oracle.com