Load a JSON File of Line-Delimited Documents into a Collection

For loading data from collections in the Cloud, you must first store your object storage credentials in your Autonomous Database and then use the procedure DBMS_CLOUD.COPY_COLLECTION to load documents into a collection.

This example loads JSON values from a line-delimited file and uses the JSON file myCollection.json. Each value, each line, is loaded into a collection on your database as a single document.

Here's an example of such a file. It has three lines, with one object per line. Each of those objects gets loaded as a separate JSON document.

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

Before loading the data from myCollection.json into your database, copy the file to your object store:

Create a bucket in the object store. For example, create an Oracle Cloud Infrastructure Object Storage bucket from the Oracle Cloud Infrastructure Object Storage link, and then in your selected compartment click Create Bucket, or use a command such as the following OCI CLI command to create a bucket:
```
oci os bucket create --name fruit_bucket -c <compartment id>
```
Copy the JSON file to your object store bucket. For example use the following OCI CLI command to copy the JSON file to the fruit_bucket on Oracle Cloud Infrastructure Object Storage:
```
oci os object put --bucket-name fruit_bucket \
                  --file "myCollection.json"
```

Load the JSON file from object store into a collection named fruit on your database as follows:

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.

Creating a credential to access Oracle Cloud Infrastructure Object Store is not required if you enable resource principal credentials. See Use Resource Principal to Access Oracle Cloud Infrastructure Resources for more information.

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 => 'fruit',
    credential_name => 'DEF_CRED_NAME',
    file_uri_list   =>
      'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/fruit_bucket/o/myCollection.json',
    format          =>
      JSON_OBJECT('recorddelimiter' value '''\n''')  );
END;
/
```
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 while 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.
Where namespace-string is the Oracle Cloud Infrastructure object storage namespace and fruit_bucket is the bucket name. See Understanding Object Storage Namespaces and Overview of Object Storage for more information.

For detailed information about the parameters, see COPY_COLLECTION Procedure.
The collection fruit on your database now contains one document for each line in the file myCollection.json.

Parent topic: Load JSON on Autonomous Database

Oracle Cloud Infrastructure Documentation