Hello everyone, I have a process model that is using a custom smart service to perform web service calls. The below json is the response I'm getting. I need to store these results into a database table. Please provide me some guidance on what would be the best approach on parsing this json string into a CDT.
{ "id":"123", "status": "working", "reports":[ { "name": "report1", "status": "complete", "_links": [ { "type": "csv", "href": "http://..../report1.csv" } ] }, { "name": "report2", "status": "errored", "reason": "Something horrible occurred..." "_links": [ ] }, { "name": "report3", "status": "working", "_links": [ ] }, { "name": "report4", "status": "pending", "_links": [ ] }, ]}
Thank you!
Discussion posts and replies are publicly visible
Hi mannym,
As you have two levels of nested CDTs, its better to create Independent CDTs and have each Child CDT have a column referring to its Parent(FK Relationship) and create the respective Tables like below;
MAIN, REPORT, LINK
CREATE TABLE LINK (LINK_ID INT(11) NOT NULL AUTO_INCREMENT , LINK_TYPE VARCHAR(255) DEFAULT NULL , HREF VARCHAR(255) DEFAULT NULL , REPORT_ID INT(11) NOT NULL , PRIMARY KEY (LINK_ID)); CREATE TABLE REPORT (REPORT_ID INT(11) NOT NULL AUTO_INCREMENT , NAME VARCHAR(255) DEFAULT NULL , STATUS VARCHAR(255) DEFAULT NULL , REASON VARCHAR(5000) DEFAULT NULL , MAIN_ID INT(11) NOT NULL , PRIMARY KEY (REPORT_ID)); CREATE TABLE MAIN (MAIN_ID INT(11) NOT NULL AUTO_INCREMENT, STATUS VARCHAR(255) DEFAULT NULL);
If you are using MySQL Database, you can create one Stored Procedure and pass this JSON String as an input parameter to it. Inside the SP, you can parse the JSON and store the values in to the appropriate tables. You can call this SP using Execute Stored Procedure Smart Service from the Appian.
For parsing JSON in MySQL, please follow the below link;
Thank you,
Arun