ResultSet to JSON

Last post 01-16-2020, 9:09 PM by cwsunderland. 3 replies.
Sort Posts: Previous Next
  • ResultSet to JSON
    Posted: 01-16-2020, 5:21 AM

    Hi Everybody

     

    I'm Writing a Workflow that should be accessed via REST-API (wapi) and return some Table Content.

    Have the SQL Queries and their ResultSets and now would like to format that to JSON for the Workflow Output. Tried putting it out in DataRow Format but that seems complicated to parse lateron.

    Any ideas on how to easily get a ResultSet converted to something like this:

    {
    "ClientName":"zfsti0",
    "ClientHostname":"zfsti0.pnet.ch",
    "AgentTypeName":"File System",
    "InstanceName":"DefaultInstanceName",
    "BackupSetName":"defaultBackupSet",
    "SubclientName":"default",
    "Retention":"2W",
    "SubclientPolicyName":"SQL Server"
    }
     
    Cheers Stefan
  • Re: ResultSet to JSON
    Posted: 01-16-2020, 9:28 AM

    Hey Stefan, we are looking at adding a new activity in the future to convert result set data into json.

    For now you can use a Script activity to do this conversion.

    If you want to convert a single row of data to a json object, you can use the following script

    import org.json.JSONObject;
    import workflow.types.data.*;

    DataSet ds = xpath:{/workflow/CommServDBQuery_1/resultSets}.get(0);
    DataRow row = ds.getRows().get(0);
    JSONObject obj = new JSONObject();
    for (int i=0;i<row.getColumnCount();i++) {
    obj.put(row.getColumnName(i),row.get(i));
    }
    return obj.toString();

    This will output a json object like from your example.

    If you need multiple rows converted to a json array, then you can use a script like such

    import org.json.JSONObject;
    import org.json.JSONArray;
    import workflow.types.data.*;

    DataSet ds = xpath:{/workflow/CommServDBQuery_1/resultSets}.get(0);
    JSONArray array = new JSONArray();
    for (DataRow row : ds.getRows()) {
    JSONObject obj = new JSONObject();
    for (int i=0;i<row.getColumnCount();i++) {
    obj.put(row.getColumnName(i),row.get(i));
    }
    array.put(obj);
    }

    return array.toString();

     

    Regards

     

  • Re: ResultSet to JSON
    Posted: 01-16-2020, 10:15 AM

    wow, after searching the whole Day it can be that simple, thank you very much for the script!

    now I just need to figure out how to persuade Workflow Outputs to not encapsulate everything in quotation marks and to keep existing ones unescaped, something for tomorrow :-)

  • Re: ResultSet to JSON
    Posted: 01-16-2020, 9:09 PM

    In SP18 we did fix the json within json issue your currently are seeing.

    For now you can just parse the main output you get from the web api and then parse out the individual json object values inside it.

    If you need help with this, you can send me the response you are getting and what you are currently trying to do.

     

     

The content of the forums, threads and posts reflects the thoughts and opinions of each author, and does not represent the thoughts, opinions, plans or strategies of Commvault Systems, Inc. ("Commvault") and Commvault undertakes no obligation to update, correct or modify any statements made in this forum. Any and all third party links, statements, comments, or feedback posted to, or otherwise provided by this forum, thread or post are not affiliated with, nor endorsed by, Commvault.
Commvault, Commvault and logo, the “CV” logo, Commvault Systems, Solving Forward, SIM, Singular Information Management, Simpana, Commvault Galaxy, Unified Data Management, QiNetix, Quick Recovery, QR, CommNet, GridStor, Vault Tracker, InnerVault, QuickSnap, QSnap, Recovery Director, CommServe, CommCell, SnapProtect, ROMS, and CommValue, are trademarks or registered trademarks of Commvault Systems, Inc. All other third party brands, products, service names, trademarks, or registered service marks are the property of and used to identify the products or services of their respective owners. All specifications are subject to change without notice.
Close
Copyright © 2020 Commvault | All Rights Reserved. | Legal | Privacy Policy