MagicParseJSON skip header

I have managed to authenticate an API and am getting JSON data however due to the fact that it has a header, magicparse puts all the data onto one long row.


Is there a way to skip the overarching title below of "data":  because the first column I want is Volume 


{"data":[{"volume":12298875,"volume_compliant":12062210,"volume_not_aligned":236665,"volume_not_passed":78825,"compliance":{"spf":{"compliant":11281998,"not_aligned":374885,"failed":641992},"dkim":{"compliant":12055102,"not_aligned":0,"failed":243773}},"domain":"","firstdate":"2018-12-12 13:03:06","lastdate":"2019-09-12 12:38:10","created_at":"2018-12-12 13:03:06","updated_at":"2019-09-12 12:38:10","nr_sub_domains":1,"main_domain_has_volume":1,"policy":{"p":"none","pct":"100","inherited":false},"issues":[]},{"volume":3509812,"volume_compliant":3487590,"volume_not_aligned":22222,"volume_not_passed":




Best Answer

  • the Answer is to parse and then magicparse:


    var jsonRes = JSON.parse(res);



    this works great for me ?



  • Which connector is it exactly that you're using? I haven't worked much with the Domo JSON connectors but generally if working with JSON in code you would have the JSON assigned to a variable say "response" and to do what you're asking it would be response['data']. This would make the root of the JSON everything under the data node which is what you want. Depending on the connector you may or may not be able to do this but I do see advanced options  in the JSON connector for options such as 



    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Hi,


    This is a connector I am building myself.  I have the response['data'] but the first part of that data is another header so when I use magicParseJSON its all on one line because all the data is seen as being part of that one entry.

    Data preview

    data_0_volume   data_0_volume_compliant   data_0_volume_not_aligned   data_0_volume_not_passed
  • I only dabbled with the custom connector development, I mostly use the SDK to send to Domo via API so you'll have to forgive my lack of knowledge. Instead of attempting to parse it all at once maybe try looping and adding it by row? The first JSON tree you'd want to grab would be response['data'][0] which "should" give you just this 

    {"volume":12298875,"volume_compliant":12062210,"volume_not_aligned":236665,"volume_not_passed":78825,"compliance":{"spf":{"compliant":11281998,"not_aligned":374885,"failed":641992},"dkim":{"compliant":12055102,"not_aligned":0,"failed":243773}},"domain":"","firstdate":"2018-12-12 13:03:06","lastdate":"2019-09-12 12:38:10","created_at":"2018-12-12 13:03:06","updated_at":"2019-09-12 12:38:10","nr_sub_domains":1,"main_domain_has_volume":1,"policy":{"p":"none","pct":"100","inherited":false},"issues":[]}

    I know MagicParseJSON does the parsing to rows for you but I would think it should be able to parse something like that because if I was doing this in code I would be doing


    volume = response['data'][0]['volume']

    volume_compliant = response['data'][0]['volume_compliant']


    Or would that just add the column headers multiple times?


    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Hi,


    The trees/leafs options looks like it could be a solution but I dont know how to get it working to just remove the top tree.


    instead of MagicParse, I have tried to add the specific columns and then fill the fields but I dont get any contents (just the titles) 


    // Add Rows
    for(var i = 0; i < ress.length; i++){
    var DMARCDetails = ress[i].properties;
    // Adding data, cell by cell


    eg eg

  • I wish I knew who here has the most experience with custom connectors. I could definitely get you an answer doing it via the API but hopefully someone can chime in.

    **Make sure to like any users posts that helped you and accept the ones who solved your issue.**
  • Hi,


    As a possible solution is it possible to Magicparse and then try to split the parsed data?  i.e. I get one long row with about 400 of coloumns however they start "data_0_xxx" (and about 20 coloumns) "data_1_xxx"(and same 20 coloumns) eg


    the MagicParse seems to pull out the correct fields, just not getting them into the correct coloumns.