JSON Path: extract same attributes for different objects

[fseck]

Hello everyone,

I want to extract certain information from a JSON file with Knime.
I already figured out which nodes to use:

now I am stuck at the correct JSON path.
The JSON structure has a repeating pattern for different outputs.
I would like to get for example the attribute 1 from ,general data" and the third value of attribute 1 from ,width" FOR EVERY OUTPUT.

I already can extract the information for a single output, but I don’t know how to get it for each.

Has anyone got an idea how to solve that? I appreaciate any help very much!

Kind regards,
Fabian

{
“header”: {
…
},

"output 1": {
    "general data": {
        "attribute 1": 43.57116,
        "attribute 2": 43.57116,
        "attribute 3": 43.57116
    },
    "width": {
        "attribute 1": [
            0,
            1257.0,
            43.57116
        ],
        "attribute 2": [
            167,
            1257.0,
            43.57116
        ],
        "attribute 3": [
            333,
            1257.0,
            43.57116
        ]
    }  
},

"output 2": {
    "general data": {
        "attribute 1": 43.57116,
        "attribute 2": 43.57116,
        "attribute 3": 43.57116
    },
    "width": {
        "attribute 1": [
            0,
            1257.0,
            43.57116
        ],
        "attribute 2": [
            167,
            1257.0,
            43.57116
        ],
        "attribute 3": [
            333,
            1257.0,
            43.57116
        ]
    }
    
},
"output 3": {
    "general data": {
        "attribute 1": 43.57116,
        "attribute 2": 43.57116,
        "attribute 3": 43.57116
    },
    "width": {
        "attribute 1": [
            0,
            1257.0,
            43.57116
        ],
        "attribute 2": [
            167,
            1257.0,
            43.57116
        ],
        "attribute 3": [
            333,
            1257.0,
            43.57116
        ]
    }
    
}

}

[ArjenEX]

Hi @fseck

Welcome to the KNIME community! You can also achieve this directly with a JSONPath and ungroup node.

For general data, attribute 1 → use path *['general data']['attribute 1']

(Matches are automatically highlighted in blue)

For width,attribute 1, third value → use path *['width']['attribute 1'][2]

Make sure you have the List thickbox checked. If you then ungroup those two lists, you’ll get all individual values (which happens to be same for all of them in your example).

Hope this provides some inspritation!

[fseck]

Hey Arjen!

Thank you a lot, I really appreciate your help!!! I think for you it was super easy, but it was exactly what I’ve been looking for

But now I discovered a new problem:

For each repeating ,output" that I need to extract information from, there exists a condition that makes all attributes of that ,output" null:

“error”: {
“attribute 1”: true
},

If an attribute is ,null" I receive a question mark in my result table (which is ok for me). But somehow for the *[‘width’][‘attribute 1’][2] I don’t get a question mark, but the next found result (which does belong to the next output that has a value for that requested attribute). I hope that was formulated so you understand it.

For example: I have 10 outputs. Output #5,6,7 have null attributes bc of the true condition. Then the rows #8,9,10 (last three) don’t have a value for that attribute from *[‘width’][‘attribute 1’][2] because it was assigned to #5,6,7 (even though their attribute is null).

Do you have any idea how I can solve that? A question mark would be sufficient for me.

Very kind regards,
Fabian

[ArjenEX]

Do you have another example json that includes such a scenario? Preferably one or two outputs from each type (normal one and the null attributes)

The path needs to be adjusted accordingly because it’s probably too generic right now but that’s a guessing game without seeing it I’m afraid. Most likely it needs to be done in multiple steps.

[fseck]

“output 1”: {

    "error": {
        "attribute 1": true
    },
    "general data": {
        "attribute 1": null,
        "attribute 2": null,
        "attribute 3": null
    },
    "width": {
        "attribute 1": null,
        "attribute 2": null,
        "attribute 3": null
    },
},

"output 2": {
    "error": {
        "attribute 1": false
    },
    "general data": {
        "attribute 1": 19.942478,
        "attribute 2": 18.22136,
        "attribute 3": 23.00542
    },
    "width": {
        "attribute 1": [
            0,
            1174.0,
            18.22136
        ],
        "attribute 2": [
            167,
            1165.5,
            18.3929
        ],
        "attribute 3": [
            333,
            1164.0,
            18.75504
        ]
    }
},

"output 3": {
    "error": {
        "attribute 1": false
    },
    "general data": {
        "attribute 1": 17.942478,
        "attribute 2": 19.22136,
        "attribute 3": 22.00542
    },
    "width": {
        "attribute 1": [
            0,
            1175.0,
            16.22136
        ],
        "attribute 2": [
            168,
            1167.5,
            17.3929
        ],
        "attribute 3": [
            334,
            1165.0,
            17.75504
        ]
    }
}

It would look like this.

[ArjenEX]

@fseck Thanks! As expected, can be achieved with one additional path query.

Change the width attribute to *['width']['attribute 1'] of output type JSON. That will pickup the entire array of values, including the null.

Ungroup it again and then use another JSONPath node to get the third element again from the array (if it exists) with $[2]

WF:
JSON Path extract same attributes for different objects.knwf (22.3 KB)

[fseck]

first of all thank you!!

actually I would like to extract not only the value *[‘width’][‘attribute 1’][2] from attribute 1, but also from 2 and 3.
Using the way you proposed, I would get several (in this case 3) json format results that I would need to use the Ungroup node on. But as far as I know, a Ungroup node only works for one json. So I would need to use 3 of them??? Or can you think of a better solution?

I am sorry for the amount of questions, but I have very few experience with json and Knime in general!

[ArjenEX]

@fseck

But as far as I know, a Ungroup node only works for one json. So I would need to use 3 of them???

You don’t. Just add as many queries as you like to the JSONpath. For example:

In the ungroup node, move all columns to the include section and select enforce exclusion. That way all new columns of type list are automatically included.

Example:

[fseck]

I was talking about the second JSON Path node.

Then I get a warning that there are multiple JSON columns (because now we have 3 lists of 3 values each) and I have to select one as the input.

[ArjenEX]

I see. Then I would put a JSON Column Combiner in between and query that. This way is a lot more dynamic and easier to manage compared to putting multiple nodes in there that do the same kind of operation.

Output:

WF V2:
JSON Path extract same attributes for different objects v2.knwf (36.1 KB)

[fseck]

Hey Arjen!

that works perfectly for me thank you, again.
You really helped, I am glad this forum and people like you who help exist.

If some new questions pop up, I will get back to this topic.

Have a good weekend!

[Daniel_Weikert]

Hi @ArjenEX
could you quickly explain the difference between $. and * (asterisk) for selecting json parts?
thanks