Need help using tExtractJSONFields to parse JSON input

Anonymous — Thu, 18 May 2017 16:06:28 GMT

Hello Talend Community!

I'm having trouble parsing a JSON file that is formatted as follows:

{
  "meta": {
    "dates": [
      "2017-01-01",
      "2017-01-02"
    ]
  },
  "data": {
    "2017-01-01": {
      "steps": [
        {
          "count": 14730,
          "step_conv_ratio": 1,
          "goal": "Annubis-land",
          "overall_conv_ratio": 1,
          "avg_time": null,
          "event": "Annubis-land"
        },
        {
          "count": 6999,
          "step_conv_ratio": 0.475152749490835,
          "goal": "Annubis-go",
          "overall_conv_ratio": 0.475152749490835,
          "avg_time": 323,
          "event": "Annubis-go"
        },
        {
          "count": 933,
          "step_conv_ratio": 0.13330475782254608,
          "goal": "PA-formgen-success",
          "overall_conv_ratio": 0.06334012219959267,
          "avg_time": 407,
          "event": "PA-formgen-success"
        }
      ],
      "analysis": {
        "completion": 933,
        "starting_amount": 14730,
        "steps": 3,
        "worst": 2
      }
    },
    "2017-01-02": {
      "steps": [
        {
          "count": 31111,
          "step_conv_ratio": 1,
          "goal": "Annubis-land",
          "overall_conv_ratio": 1,
          "avg_time": null,
          "event": "Annubis-land"
        },
        {
          "count": 16219,
          "step_conv_ratio": 0.5213268618816496,
          "goal": "Annubis-go",
          "overall_conv_ratio": 0.5213268618816496,
          "avg_time": 201,
          "event": "Annubis-go"
        },
        {
          "count": 2181,
          "step_conv_ratio": 0.1344719156544793,
          "goal": "PA-formgen-success",
          "overall_conv_ratio": 0.07010382179936357,
          "avg_time": 554,
          "event": "PA-formgen-success"
        }
      ],
      "analysis": {
        "completion": 2181,
        "starting_amount": 31111,
        "steps": 3,
        "worst": 2
      }
    }
  }
}

I'm interested in parsing everything contained in the "data" key, and I need to loop over each date and then each step to get: date - step - count - step_conv_ratio - etc...

The problem is that the dates are key names, and I don't know how to get this to work.

Could someone here help me or tell me if this is doable using JsonPath or Xpath?

I tried a workaround by dealing with the whole file in a routine, by using a JSON parser but I can't neither get this to work (can't load the correct library for JSON parsing). Anyway let's try to solve the problme with the tExtractJSONFields component first!

Thx!

Re: Need help using tExtractJSONFields to parse JSON input

vapukov — Thu, 18 May 2017 19:51:24 GMT

You can use JSON API library for this - java-son-api-1.0.jar

tJavaFlex code:

JSONObject object = new JSONObject(row4.line);
String[] keys = JSONObject.getNames(object);

for (String key : keys)
{
    Object value = object.get(key);
}

globalMap.put("array_size", keys.length);

List keylist = Arrays.asList(keys);
globalMap.put("vk",keylist);

in advanced tab:

import org.json.*;
import java.util.*;

than in next tJavaFlex - select next KEY for JSON parse:

//System.out.println(globalMap.get("vk"));

List<String> alist = (List<String>)globalMap.get("vk");

Integer ii = ((Integer)globalMap.get("tLoop_1_CURRENT_ITERATION"))-1;

//row18.jkeys=alist.get(ii);
globalMap.put("jkey",alist.get(ii));

topic Re: Need help using tExtractJSONFields to parse JSON input in Talend Studio

Need help using tExtractJSONFields to parse JSON input

Re: Need help using tExtractJSONFields to parse JSON input