Hi Team,
In one of our table we have column which holds JSON format text. data will be inserted to this column from a file we receive from Vendor. While Inserting the data to this column we dont't validate whether its in JSON format or not but before we parse this JSON we should validate the format.
I am using IS JSON utility provided by Oracle but its working only for single JSON. If there is any JSON kind of nested json (json with in json) its not working. Even the Json is in correct format its saying invalid Json.
Please help me how to check the format is in JSON or not.
create table test1108(appno number,application varchar2(4000));
insert into test1108 VALUES (1,'{"id":"1111","date":"2017-11-08","amount":4000,"action":"REVIEW","examinerActions":[{"examinername":"ABASU","action":"REVIEW","time":"2017-11-0114:58:06.699Z"}],"finalAction":"REVIEWED","qualification":[{"degree":"MCA","score":200,"grade":"A++"},{"degree":"MTECH","score":500,"grade":"A"},{"degree":"BCOM","score":2000,"grade":"B"}],"status":"APPROVED"
}');
INSERT INTO TEST1108 VALUES (2,'[{"id":"1234","date":"2017-11-07","amount":5000,"action":"INVESTIGATE","examinerActions":[{"examinername":"PRAKESH","action":"REJECT","time":"2017-11-0714:58:06.699Z"}],"finalAction":"CANCEL","qualification":[{"degree":"MBA","score":100,"grade":"A"},{"degree":"MCA","score":0,"grade":"NONE"},{"degree":"BSC","score":2000,"grade":"A"}],"status":"CANCELLED"
}
{"id":"6789","date":"2017-11-08","amount":10000,"action":"APPROVE","examinerActions":[{"examinername":"RAHUL","action":"APPROVE","time":"2017-11-0714:58:06.699Z"}],"finalAction":"APPROVED","qualification":[{"degree":"MCA","score":200,"grade":"A++"},{"degree":"MTECH","score":500,"grade":"A"},{"degree":"BCOM","score":2000,"grade":"B"}],"status":"APPROVED"
}]');
INSERT INTO TEST1108 VALUES (3,'{"id":"2222","date":"2017-10-07","amount":5000,"action":"APPRVOE","examinerActions":[{"examinername":"ANN","action":"REJECT","time":"2017-11-0714:58:06.699Z"}],"finalAction":"CANCEL","qualification":[{"degree":"MBA","score":100,"grade":"A"},{"degree":"MCA","score":0,"grade":"NONE"},{"degree":"BSC","score":2000,"grade":"A"}],"status":"CANCELLED"
}
{"id":"6789","date":"2017-11-08","amount":10000,"action":"APPROVE","examinerActions":[{"examinername":"RAHUL","action":"APPROVE","time":"2017-11-0714:58:06.699Z"}],"finalAction":"APPROVED","qualification":[{"degree":"MCA","score":200,"grade":"A++"},{"degree":"MTECH","score":500,"grade":"A"},{"degree":"BCOM","score":2000,"grade":"B"}],"status":"APPROVED"
}');
SELECT appno FROM TEST1108 where application is json;
1
That's because they're not valid JSON!
If you want to have multiple JSON objects, you need to wrap them in an array (square brackets):
create table test1108(appno number,application varchar2(4000));
insert into test1108 VALUES (1,'{"id":"1111","date":"2017-11-08","amount":4000,"action":"REVIEW","examinerActions":[{"examinername":"ABASU","action":"REVIEW","time":"2017-11-0114:58:06.699Z"}],"finalAction":"REVIEWED","qualification":[{"degree":"MCA","score":200,"grade":"A++"},{"degree":"MTECH","score":500,"grade":"A"},{"degree":"BCOM","score":2000,"grade":"B"}],"status":"APPROVED"
}');
INSERT INTO TEST1108 VALUES (2,'[{"id":"1234","date":"2017-11-07","amount":5000,"action":"INVESTIGATE","examinerActions":[{"examinername":"PRAKESH","action":"REJECT","time":"2017-11-0714:58:06.699Z"}],"finalAction":"CANCEL","qualification":[{"degree":"MBA","score":100,"grade":"A"},{"degree":"MCA","score":0,"grade":"NONE"},{"degree":"BSC","score":2000,"grade":"A"}],"status":"CANCELLED"
},
{"id":"6789","date":"2017-11-08","amount":10000,"action":"APPROVE","examinerActions":[{"examinername":"RAHUL","action":"APPROVE","time":"2017-11-0714:58:06.699Z"}],"finalAction":"APPROVED","qualification":[{"degree":"MCA","score":200,"grade":"A++"},{"degree":"MTECH","score":500,"grade":"A"},{"degree":"BCOM","score":2000,"grade":"B"}],"status":"APPROVED"
}]');
INSERT INTO TEST1108 VALUES (3,'[{"id":"2222","date":"2017-10-07","amount":5000,"action":"APPRVOE","examinerActions":[{"examinername":"ANN","action":"REJECT","time":"2017-11-0714:58:06.699Z"}],"finalAction":"CANCEL","qualification":[{"degree":"MBA","score":100,"grade":"A"},{"degree":"MCA","score":0,"grade":"NONE"},{"degree":"BSC","score":2000,"grade":"A"}],"status":"CANCELLED"
},
{"id":"6789","date":"2017-11-08","amount":10000,"action":"APPROVE","examinerActions":[{"examinername":"RAHUL","action":"APPROVE","time":"2017-11-0714:58:06.699Z"}],"finalAction":"APPROVED","qualification":[{"degree":"MCA","score":200,"grade":"A++"},{"degree":"MTECH","score":500,"grade":"A"},{"degree":"BCOM","score":2000,"grade":"B"}],"status":"APPROVED"
}]');
SELECT appno FROM TEST1108 where application is json;
APPNO
1
2
3