Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
PDI Groovy script to flatten JSON into key/value pairs, with the JSON path being the key
@Grab(group='com.fasterxml.jackson.core', module='jackson-databind', version='2.3.3')
import com.fasterxml.jackson.databind.JsonNode
import com.fasterxml.jackson.databind.ObjectMapper
import com.fasterxml.jackson.databind.node.ArrayNode
import com.fasterxml.jackson.databind.node.ObjectNode
import com.fasterxml.jackson.databind.node.ValueNode
import org.pentaho.di.core.row.*
import org.pentaho.di.core.row.value.*
def addKeys(String currentPath, JsonNode jsonNode, Map<String, String> map) {
if (jsonNode.isObject()) {
ObjectNode objectNode = (ObjectNode) jsonNode
Iterator<Map.Entry<String, JsonNode>> iter = objectNode.fields()
pathPrefix = currentPath.isEmpty() ? "" : currentPath + ".";
while (iter.hasNext()) {
Map.Entry<String, JsonNode> entry = iter.next();
addKeys(pathPrefix + entry.getKey(), entry.getValue(), map);
}
} else if (jsonNode.isArray()) {
ArrayNode arrayNode = (ArrayNode) jsonNode;
for (int i = 0; i < arrayNode.size(); i++) {
addKeys(currentPath + "[" + i + "]", arrayNode.get(i), map);
}
} else if (jsonNode.isValueNode()) {
ValueNode valueNode = (ValueNode) jsonNode;
map.put(currentPath, valueNode.asText());
}
}
map = [:]
try {
node = new ObjectMapper().readTree(json)
addKeys("", node, map );
} catch (IOException e) {
e.printStackTrace();
}
outputRowMeta = _step_.getInputRowMeta().clone();
_step_.stepMeta.stepMetaInterface.getFields( outputRowMeta, _step_.getStepname(), null, null, _step_, _step_.repository, _step_.metaStore );
outputRowMeta.addValueMeta(new ValueMetaString("key"))
outputRowMeta.addValueMeta(new ValueMetaString("value"))
outputRow = RowDataUtil.resizeArray( row, outputRowMeta.size()+2 )
// Inject at the end
int outputIndex = rowMeta.size()
int count = 1
int numProps = map.size()
key = null
value = null
map.each {kv ->
if(count < numProps) {
keyIndex = outputIndex
valueIndex = outputIndex+1
if(keyIndex >= 0 && valueIndex >= 0) {
outputRow[keyIndex] = kv.key
outputRow[valueIndex] = kv.value
}
_step_.putRow(outputRowMeta, outputRow)
}
else {
key = kv.key
value = kv.value
}
count++
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.