I am currently reading the pretty json files from S3 bucket.
Here is what i am doing.
drop table JsonReadExt;
begin
DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
table_name =>'JsonReadExt',
credential_name =>'TESTCRED',
file_uri_list =>'https://s3bucket.amazonaws.com/sample2*.json',
format => '{"skipheaders":"0", "ignoreblanklines":"true", "rejectlimit":100}',
field_list=> 'jsonValue ',
column_list => 'jsonValue clob ');
END;
/
ALTER TABLE JsonReadExt ACCESS PARAMETERS (records delimited by 'eof'
CREDENTIAL 'TESTCRED'
FIELDS (JsonValue CHAR(200000))
);
The reason i am doing alter table cause the records delimiter doesn't seems to like "eof" as part of table creation. I might be doing something wrong.
Sample json files are
Sample2.json
{
"firstName": "Joe",
"lastName": "Jackson",
"gender": "male",
"age": 28,
"address": {
"streetAddress": "101",
"city": "San Diego",
"state": "CA"
},
"phoneNumbers": [
{ "type": "home", "number": "7349282382" }
]
}
sample2a.json
{
"firstName": "Joe",
"lastName": "Jackson",
"gender": "male",
"age": 28,
"address": {
"streetAddress": "101",
"city": "San
Diego",
"state": "CA"
},
"phoneNumbers": [
{ "type": "home", "number": "7349282382" }
]
}
select * from JsonReadExt
<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/FBX5LU1I5PZ5/image.png" alt="image.png">select * from JsonReadExt, json_table(jsonValue,'$' columns city varchar2(2000) path '$."address"."city"'
) jT;
<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/4ITR2C323WB2/image.png" alt="image.png">Only 1 row is returned.
select json_Query(jsonValue,'$."address"."city"' WITH WRAPPER) from JsonReadExt;
<img src="https://objectstorage.us-phoenix-1.oraclecloud.com/p/BqK85Rn1zA5MP0vYiqbAdPgs7Z6OmMxw8SD3WCFVm5kY8uReidZ1KPIKkgJ1hCkG/n/axciphqpnohg/b/forums-legacy/o/uploads/EQMUZKGC2P86/image.png" alt="image.png">
for Sample2a.json i need to remove the new line characters between the Quotes so i can read it as part of json_table. Also need to replace it with \\n so that when the value is copied/inserted into the oracle table it will retain the new line character.
I am no good with regular expression. Tried googling it but still learning.
Please advise.
Thanks
Alvinder