How to extract something from JSON data

KNIME Analytics Platform
1

Hi, after building several workflows i got stuck the first time, after trying to solve the problem alone..

I have, lets say, 2 columns and 2 rows, that look like this:

  STRING JSON
row0 xyz1 
{
  "data" : [ {
     "outputs" : [ {
         "hash" : "xyz1",
         "hash" : "xyz2",
     } ],
     "outputs" : [ {
         "hash" : "xyz3",
         "hash" : "xyz4",
         "hash" : "xyz5",
     } ],
}
row1 abc4 
{
  "data" : [ {
     "outputs" : [ {
         "hash" : "abc1",
         "hash" : "abc2",
     } ],
     "outputs" : [ {
         "hash" : "abc3",
         "hash" : "abc4",
         "hash" : "abc5",
     } ],
}

 

Now i want to extract all "outputs" data, that contain the string value from the other column, which should look like this:

  STRING JSON RESULT
row0 xyz1 
{
  "data" : [ {
     "outputs" : [ {
         "hash" : "xyz1",
         "hash" : "xyz2",
     } ],
     "outputs" : [ {
         "hash" : "xyz3",
         "hash" : "xyz4",
         "hash" : "xyz5",
     } ],
}
 
     "outputs" : [ {
         "hash" : "xyz1",
         "hash" : "xyz2",
     } ],
row1 abc4 
{
  "data" : [ {
     "outputs" : [ {
         "hash" : "abc1",
         "hash" : "abc2",
     } ],
     "outputs" : [ {
         "hash" : "abc3",
         "hash" : "abc4",
         "hash" : "abc5",
     } ],
}
 
     "outputs" : [ {
         "hash" : "abc3",
         "hash" : "abc4",
         "hash" : "abc5",
     } ],
		<p>&nbsp;</p>
		</td>
	</tr>
</tbody>

Has anybody an idea how to solve this?

Thank you very much in advance.

2

Hi,

I would select the outputs as collections (using the JSONPath node). From those collections, you might need to split them to multiple columns, though it is not necessary if you are using Java Snippets (JSON values are Strings too). After that you need to convert the JSON values as Strings back to JSON values using the String to JSON node. I have not tried this, but it should work.

I guess there could be more direct way if there were a filtering option based on another column's value in the Column Aggregator node, which case the conversion to and from were not necessary (it seems not too hard to implement, though probably not too intuitive).

Cheers, gabor