Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Replacing new line character in the Json between the quotes

alvinderJul 21 2022 — edited Jul 21 2022

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

This post has been answered by Paulzip on Jul 21 2022
Jump to Answer
Comments
Post Details
Added on Jul 21 2022
4 comments
2,487 views