Search code examples
jsonexcelvbajscriptscriptcontrol

Remove layers (keys) from heavily nested JSON in JSCRIPT/VBA

I am parsing a heavily nested JSON in VBA, using scriptcontrol/jscript. The resulting JSON object is super nested, and has recurring 'useless' levels / layers called 'buckets'.

Is there a way I can remove these collectively from either my json string or the parsed json object?

Imagine it something like this:

responses.0.buckets.0.aggregations.0.10.buckets.0.5.buckets.0.9.buckets.0.20.buckets.0.8.buckets.0.13.buckets.0.14.buckets.0.15.buckets.0.16.buckets.0.19.buckets.0.18.buckets.0.21.doc_count_error_upper_bound

I'd only need the 'doc_count_error_upper_bound' value, and could essentially do without all the 0s and without all the buckets, making it less nested into:

responses.aggregations.10.5.9.20.8.13.14.15.16.19.18.21.doc_count_error_upper_bound

This would still be pretty heavily nested, but saves me a lot of headaches already. I just do not know how I could do this with jscript/scriptcontrol in VBA (es3).

The source data is coming from a Kibana dashboard (examples on http://demo.elastic.co/ )

Thanks for any help!

Jasper

UPDATE: Question regarding VBA code - the VBA code I have is irrelevant, as it's the standard way of loading a json string into an object via scriptcontrol.

I do not use EVAL, but for example purposes, it would be something like the below:

Dim Scr as Object, Json as Object
Set Scr = CreateObject("Scriptcontrol")
Scr.Language = "Jscript"

Set Json = Scr.Eval("(" & WinHTTP.ResponseText & ")")

I cannot share an example of the JSON string, as it contains sensitive data. But ultimately, that's beside the question. Consider example https://adobe.github.io/Spry/data/json/donuts.js

On the top there, is "batter" as key in between "batters" and the different IDs. If I'd want to remove that key, but keep the underlying ID data - how would I do that, through a js scrip that works in scriptcontrol in VBA?

UPDATE:
omegastripes answer worked very well, however, I failed to realize that a number of the keys I wanted to remove (the 'buckets' and '0' etc) had keys and values under them.

Let's take the example of the donuts, just altered a bit - see here: https://pastebin.com/WxYir7vK

now I would want to remove the '0', '1', '2' etc keys without losing the underlying sub-keys. However, for omegastripes code to work, I'd have to delete keys 'sequence', 'variant', 'name', and 'ppu' from all layers / throughout the json.

I can do that for one of them, for one layer with the function below:

function unseat(obj, prop) { for(var k in obj[prop]) obj[k] = obj[prop][k]; delete obj[prop]; return obj; }

And then calling the functio 'unseat (JSONObj, "variant")' - this works, but only for one of the four variables at a time and only for one layer. How can I alter this so that I can remove it throughout the object, for all four at once, so that afterwards I can use omegastripes code to unwrap.

Summary
1) I take this json string: https://pastebin.com/WxYir7vK 2) parse it into script control into VBA
3) loop through it and remove all 'sequence', 'variant', 'name' and 'ppu' key/value pairs
4) unwrap it via omegastripes code.

Step 1 / 2 and 4 are taken care of - but how to do 3?

Thanks!

Solution

  • Using ScriptControl for parsing JSON has the following shortcomings (check this answer for details):

    • System environment is exposed to malware code injections received within response.
    • ScriptControl is not available on 64-bit MS Office.

    Anyway if you are confident that operating in JScript environment is the only way, you may unwrap excessive nesting of objects and arrays structure using the below functions:

    function gParse(sample) {
    return eval('(' + sample + ')');
};

function gUnwrap(sample) {
    for (var key in sample) {
        sample[key] = gUnwrap(sample[key]);
    };
    var count = 0;
    for (var key in sample) {
        count++;
        if (count == 2) break;
    };
    if (count == 1) {
        var type = gGetType(sample);
        if (type == 'Array' || type == 'Object') {
            var type = gGetType(sample[key]);
            if (type == 'Array' || type == 'Object') {
                return sample[key];
            }
        }
    };
    return sample;
};

function gGetType(sample) {
    return {}.toString.call(sample).slice(8, -1);
};

    That could be done in VBA as shown below:

    Option Explicit

Sub Test()

    Dim sJSON As String
    Dim ParseJSON As Object
    Dim UnwrapJSON As Object
    Dim oJSON As Object

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://adobe.github.io/Spry/data/json/donuts.js", False
        .send
        sJSON = .responseText
    End With
    With CreateObject("htmlfile")
        With .parentWindow
            .execScript "function gParse(sample) {return eval('(' + sample + ')')};"
            .execScript "function gUnwrap(sample) {for (var key in sample) {sample[key] = gUnwrap(sample[key]);}; var count = 0; for (var key in sample) {count++; if (count == 2) break;}; if (count == 1) {var type = gGetType(sample); if (type == 'Array' || type == 'Object') {var type = gGetType(sample[key]); if (type == 'Array' || type == 'Object') {return sample[key];}}}; return sample;};"
            .execScript "function gGetType(sample) {return {}.toString.call(sample).slice(8, -1)};"
            Set ParseJSON = .gParse
            Set UnwrapJSON = .gUnwrap
        End With
    End With
    Set oJSON = UnwrapJSON(ParseJSON(sJSON))

End Sub

    The locals window shows JSON object for the sample you provided as follows:

    source

    And unwrapped JSON object:

    result