Oracle NoSQL database : How to import json documents in a specific column?

Question

I have a question about the import command in the Oracle NoSQL Database - SQL shell tool. I am wondering if there is an option to read a file and put the data in a JSON column instead of put the data in a table. Currently, it is matching the fields from the JSON file to the columns in the table. Something like

import -table <table_name> - column <myjsoncolumn> -file <myfile>

Here an example - simplified version

sql-> create table stat (reportTime long, reportTimeHuman string , primary key (reportTime));
Statement completed successfully
sql-> import -table stat -file file.json
Loaded 736 rows to stat.
sql-> select * from stat limit 5;
{"reportTime":1624370080000,"reportTimeHuman":"2021-06-22 13:54:40.000 UTC"}
{"reportTime":1624366760000,"reportTimeHuman":"2021-06-22 12:59:20.000 UTC"}
{"reportTime":1624368660000,"reportTimeHuman":"2021-06-22 13:31:00.000 UTC"}
{"reportTime":1624370980002,"reportTimeHuman":"2021-06-22 14:09:40.002 UTC"}

I want to do

CREATE TABLE IF NOT EXISTS stat 
( id INTEGER GENERATED ALWAYS AS IDENTITY, myJson JSON, PRIMARY KEY (id))
import -table stat -column myJSON -file file.json
sql-> select myJson from stat limit 5;
{"reportTime":1624370080000,"reportTimeHuman":"2021-06-22 13:54:40.000 UTC"}
{"reportTime":1624366760000,"reportTimeHuman":"2021-06-22 12:59:20.000 UTC"}
{"reportTime":1624368660000,"reportTimeHuman":"2021-06-22 13:31:00.000 UTC"}
{"reportTime":1624370980002,"reportTimeHuman":"2021-06-22 14:09:40.002 UTC"}

I am expecting to have the json documents in the myJson column. id is a generated number in this case.

Answer 1

No, there is no such option using the shell but the migrator tool can do this

Step 1: create your table

Step 2: create the following file ./migrator-export.json

{
  "source" : {
    "type" : "file",
    "format" : "json",
    "dataPath" : "/data/test/kvstore_export/"
  },
  "sink" : {
    "type" : "nosqldb",
    "storeName" : "OUG",
    "helperHosts" : ["localhost:5000"],
    "table" : "stat",
    "requestTimeoutMs" : 5000
  },
  "transforms": {
    "aggregateFields" : {
      "fieldName" : "myJson",
      "skipFields" : []
    }
  },
  "abortOnError" : true,
  "migratorVersion" : "1.0.0"
}

Step 3; execute the command Migrator using the previous configuration file.

You can also follow the wizard

Would you like to create table as part of migration process?
Use this option if you want to create table through the migration tool.
If you select yes, you will be asked to provide a file that contians table DDL or to use default schema.
(y/n) (n): y
We identified source as file.
Would you like to use below default schema?
CREATE TABLE IF NOT EXISTS stat(id LONG GENERATED ALWAYS AS IDENTITY(CACHE 5000), document JSON, PRIMARY KEY(SHARD(id)))
Where 'id' will be auto generated and 'document' is all the
fields aggregated into one JSON column.
Please note that tool will internally create table with above mentioned schema. 
For aggregation below transforms are applied
internally by the tool."transforms" : {
        "aggregateFields" : {
          "fieldName" : "document"
        }
      }
(y/n) (n): y