Load an Array of JSON Documents into a Collection

To load data from collections in the Cloud, you first store your object storage credentials in your Autonomous Database and then use PL/SQL procedure DBMS_CLOUD.COPY_COLLECTION to load documents into a collection. This topic explains how to load documents to your database from a JSON array in a file.

Note:

You can also load documents from a JSON array in a file into a collection using SODA for REST. See Load Purchase-Order Sample Data Using SODA for REST.

This example uses the JSON file fruit_array.json. The following shows the contents of the file fruit_array.json:

[{"name" : "apple", "count": 20 },
 {"name" : "orange", "count": 42 },
 {"name" : "pear", "count": 10 }]

Before loading data into Autonomous Database, copy the data to your object store as follows:

Create a bucket in the object store. For example, create an Oracle Cloud Infrastructure Object Store bucket from the Oracle Cloud Infrastructure Object Storage link, in your selected Compartment, by clicking Create Bucket, or use a command line tool such as the following OCI CLI command:
```
oci os bucket create -name json_bucket -c <compartment id>
```
Copy the JSON file to the object store. For example, the following OCI CLI command copies the JSON file fruit_array.json to the object store:
```
oci os object put --bucket-name json_bucket --file "fruit_array.json"
```

Load the JSON file from object store into a SODA collection named fruit2 on your database:

Store your object store credentials using the procedure DBMS_CLOUD.CREATE_CREDENTIAL, as shown in the following example:
```
SET DEFINE OFF
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username => 'adb_user@example.com',
    password => 'password'
  );
END;
/
```
This operation stores the credentials in the database in an encrypted format. You can use any name for the credential name. Note that this step is required only once unless your object store credentials change. Once you store the credentials, you can use the same credential name for loading all documents.

See CREATE_CREDENTIAL Procedure for detailed information about the parameters.

Note:
Some tools like SQL*Plus and SQL Developer use the ampersand character (&) as a special character. If you have the ampersand character in your password, then use the SET DEFINE OFF command in those tools as shown in the example to disable the special character, and get the credential created properly.
Load the data into a collection using the procedure DBMS_CLOUD.COPY_COLLECTION.
```
BEGIN 
  DBMS_CLOUD.COPY_COLLECTION(    
    collection_name => 'fruit2',    
    credential_name => 'DEF_CRED_NAME',    
    file_uri_list => 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/json/o/fruit_array.json',
    format => '{"recorddelimiter" : "0x''01''", "unpackarrays" : "TRUE", "maxdocsize" : "10240000"}'
  );
END;
/
```
In this example you load a single JSON value which occupies the whole file, so there is no need to specify a record delimiter. To indicate that there is no record delimiter, you can use a character that does not occur in the input file. For this example, to indicate that there is no delimiter, the control character 0x01 (SOH) is set to load the JSON documents into a collection,. Thus, you specify a value for the recorddelimiter that does not occur in the JSON file. For example, you can use value "0x''01''" because this character does not occur directly in JSON text.

When unpackarrays parameter for format value is set to TRUE, the array of documents is loaded as individual documents rather than as an entire array. The unpacking of array elements is however limited to single level. If there are nested arrays in the documents, those arrays are not unpacked.

The parameters are:
- collection_name: is the name of the target collection.
- credential_name: is the name of the credential created in the previous step. The credential_name parameter must conform to Oracle object naming conventions, which do not allow spaces or hyphens.
- file_uri_list: is a comma delimited list of the source files that you want to load.
- format: defines the options that you can specify to describe the format of the source file. The format options characterset, compression, encryption, ignoreblanklines, jsonpath, maxdocsize, recorddelimiter, rejectlimit, type, unpackarrays are supported for loading JSON data. Any other formats specified will result in an error.
  
  If the data in your source files is encrypted, decrypt the data by specifying the format parameter with the encryption option. See Decrypt Data While Importing from Object Storage for more information on decrypting data.
  
  See DBMS_CLOUD Package Format Options for more information.
In this example, namespace-string is the Oracle Cloud Infrastructure object storage namespace and bucketname is the bucket name. See Understanding Object Storage Namespaces for more information.

For detailed information about the parameters, see COPY_COLLECTION Procedure.

The load of fruit_array.json, with DBMS_CLOUD.COPY_COLLECTION using the format option unpackarrays recognizes array values in the source and instead of loading the data as a single document, as it would by default, the data is loaded in the collection fruit2 with each value in the array as a single document.

Parent topic: Load JSON on Autonomous Database