Our we use mongodb to store application data. And Oracle DBMS for datawarehousing. Our data engineer synced transaction data from mongo to oracle, where he lumped whole transaction sequenct in 1 column as clob. Now I want to extract necessary data such as userid, transaction amount, status etc from said column.
Here is an example doc:
{ "\_id" : ObjectId("652e4eea5df2d790"), "processId" : ObjectId("652e4e792883e824c0"), "sessionId" : ObjectId("652e403bc3c52bb66"), "userId" : ObjectId("61976934afe29f99df6c2"), "phoneNumber" : "99081825", "apiName" : "https://dpp-api.toki.mn/teller/v1/teller/transactions/lendP2M", "actionType" : "DPP", "status" : "SUCCESS", "request" : { "accountId" : "61976934afedf6c2", "amount" : 1500, "targetAccountIdentifier" : "63748ecb5956cce", "requestId" : "652e4ee93eb24c2", "type" : "P2M", "description" : "Payment for parking" }, "response" : { "transNumber" : "11093424", "response" : "SUCCESS" }, "error" : null, "createdAt" : ISODate("2023-10-17T14:37:54.293+05:30"), "expiresAt" : ISODate("2023-11-14T08:10:17.853+05:30"), "\__v" : 0 }
I was using json_table() function to parse it to some avail, but MongoDB doc is not exactly json. For starters, fields can take Object as value like userid: Objectid("userid") which will result in error from json_table. For now I am replacing "Objectid(" and ")" with empty string, there is also true/false flag which don't have quotation and resulting in whole query to return null result.
Q: Is there a better way to handle text in MongoDb document format in Oracle itself without resorting to python/r etc.
P.S: I am only a user with only select priviliges. So I can't define functions, procedures. I can only select from blah blah.