Search code examples
jsontalend

JSON Deserialization on Talend

Trying to figuring out how to deserialize this kind of json in talend components :

            {
          "[email protected]": {
            "confidence":119,"email":"[email protected]","default":20
          },
          "[email protected]": {
            "confidence":20,"email":"[email protected]","default":15
          },
          "[email protected]": {
            "confidence":18,"email":"[email protected]","default":16
          },
          "[email protected]": {
            "confidence":17,"email":"[email protected]","default":17
          },
          "[email protected]": {
            "confidence":16,"email":"[email protected]","default":18
          },
          "[email protected]": {
            "confidence":15,"email":"[email protected]","default":19
          },
          "[email protected]": {
            "confidence":14,"email":"[email protected]","default":14
          },
          "[email protected]": {
            "confidence":13,"email":"[email protected]","default":13
          }
        }

This JSON comes from the Toofr API where documentation can be found here .

Here the actual sitation : enter image description here

For each line retreived in the database, I call the API and I got this (the first name, the last name and the company change everytime.

Does anyone know how to modify the tExtractJSONField (or use smthing else) to show the results in tLogRow (for each line in the database) ?

Thank you in advance !

EDIT 1:

Here's my tExtractJSONfields :

enter image description here

Solution

  • When using tExtractJSONFields with XPath, you need

    1) a valid XPath loop point

    2) valid XPath mapping to your structure relative to the loop path

    Also, when using XPath with Talend, every value needs a key. The key cannot change if you want to loop over it. Meaning this is invalid:

          {
      "[email protected]": {
        "confidence":119,"email":"[email protected]","default":20
      },
      "[email protected]": {
        "confidence":20,"email":"[email protected]","default":15
      },

    but this structure would be valid:

          {
      "contact": {
        "confidence":119,"email":"[email protected]","default":20
      },
      "contact": {
        "confidence":20,"email":"[email protected]","default":15
      },

    So with the correct data the loop point might be /contact.

    Then the mapping for Confidence would be confidence (the name from the JSON), the mapping for Email would be email and vice versa for default.

    EDIT

    JSONPath has a few disadvantages, one of them being you cannot go higher up in the hierarchy. You can try finding out the correct query with jsonpath.com

    The loop expression could be $.*. I am not sure if that will satisfy your need, though - it has been a while since I've been using JSONPath in Talend because of the downsides.