Transforming JSON and add data

KNIME Extensions Text Processing
#1

Hi all.

I have a data table with a JSON column that looks like:

{
  "Id" : "XXXXXX",
  "Field1" : "Value1",
  "Field2" : "Value2"
}

I need to convert this column in a new column like this:

{
  "header":
  {
                "clientTime":"$TIMESTAMP"
  },
 
  "data":
  {
    "properties":
    {
       "Id" : "XXXXXX",
       "Field1" : "Value1",
       "Field2" : "Value2",
       "entityType":"platform"
    }
  }
}

Where i must include a current TIMESTAMP in the clientTime object field and an "entityType":"platform" inside properties block. I tried with JSON Transform node and doesn't work. 

Any suggestion?

Thanks!

#2

Hi all.

I can't find a proper solution, i think that the trick would be use a java snippet. Any other suggestion? Please help me.

#3

Hi,

honestly I would not bother with JSON Transformations, you can easily get it done through String Manipulations and replacement. See the attached workflow.

I didn't use RegEx to keep it simple. By using RegEx I could have probably saved a couple of nodes.

Cheers,
Marco.

#4

Dear @marco_ghislanzoni,

Your comments are very helpful, thank you for supporting me. Following your concept, i think that "String Manipulation" is the proper solution to my issue. Only one question, i am using something like this:

join("{ \"header\": {\"clientTime\":\"$clientTime$\"},\"data\": {\"properties\": {", $CODIGO$, ",\"entityType\":\"platform\"}}}")

$clientTime$ is a column with data. After run, the output shows the same $clientTime$ name and not the value. i tried with single quotes and nothing. Do you can please tell me how to fix? I suppose that is easy but i dont't know how.

Thanks!

#5

Solved!

\"", $clientTime$, "\"

:)

#6

Well done! :-)