jsonpath of elements of dynamic arrays in an array

Hi,
I have a a json with an array that includes dynamic number of arrays.
with jsonpath in java i would like to extract the n-th element of each inner array.
when I do it in online jsonpath pages it works, but with java its not working correctly.
my json looks like :
{
“data”: [
[
“1657618349.663”,
“OAUTHDB”,
“N”,
“N”,
“”,
“ACCOUNTADMIN”,
“”,
“”,
“1”
],
[
“1657276657.647”,
“SNOWFLAKE”,
“N”,
“N”,
“SNOWFLAKE.ACCOUNT_USAGE”,
“”,
“”,
“”,
“1”
],
[
“1657276660.567”,
“SNOWFLAKE_SAMPLE_DATA”,
“N”,
“N”,
“SFSALESSHARED.SFC_SAMPLES_VA3.SAMPLE_DATA”,
“ACCOUNTADMIN”,
“Provided by Snowflake during account provisioning”,
“”,
“1”
]
]
}

i want to evaluate this path : “$…data[*].[1]” but its not wokring and returning all elements of second array. seems to be the asteriks is not wokring correctly. how can i do this or how should my path looks like?
the number on inner arrays is dynamic.

Best Regards,
Majeed

Hi @msaidahm

Welcome to the KNIME community!

You were very close, but $['data'][*][1] is the right syntax here (without period). Moreover, you need to make sure that you have the List checkbox marked. That way it will capture all instances of [1], regardless how many inner arrays you have.

When you apply it, you’ll see that the JSON path will highlight all nominated elements in blue.

If you then ungroup the list, you’ll get all the records

Hope this helps!

4 Likes

Hi @ArjenEX
Thank u for ur response.
my question is how can this be done in java?
can u advise any third-party jar that can achieve this for me?

Regards,
Majeed Said

If KNIME can do this for you, why would you want to do it manually? That’s more a programming question rather than a KNIME question. There are more suitable other forums for that.

Maybe the source code contains some hints as well.

Or how KNIME uses it:

1 Like