0

i am working in Talend Open Studio and using the JIRA API, the API in JSON looks like this:

{
"sprints": [{
    "id": 10447,
    "sequence": 10447,
    "name": "SP120 - Plannibal Smith",
    "state": "CLOSED",
    "linkedPagesCount": 0
}, {
    "id": 10391,
    "sequence": 10391,
    "name": "SP119 - Don't bug or bend over",
    "state": "CLOSED",
    "linkedPagesCount": 0
}, {
    "id": 10244,
    "sequence": 10244,
    "name": "SP118 - Be an all grounder!",
    "state": "CLOSED",
    "linkedPagesCount": 0
}, {
    "id": 10183,
    "sequence": 10183,
    "name": "SP117 - The R Factor",
    "state": "CLOSED",
    "linkedPagesCount": 0
}, {
    "id": 10182,
    "sequence": 10182,
    "name": "SP116 - Deliverfull",
    "state": "CLOSED",
    "linkedPagesCount": 0
}, {
    "id": 10123,
    "sequence": 10123,
    "name": "SP115 - Appartemenneke",
    "state": "CLOSED",
    "linkedPagesCount": 0
}, {
    "id": 10048,
    "sequence": 10048,
    "name": "SP114 - two three letter words",
    "state": "CLOSED",
    "linkedPagesCount": 0
}],
"velocityStatEntries": {
    "10048": {
        "estimated": {
            "value": 15.5,
            "text": "15.5"
        },
        "completed": {
            "value": 6.0,
            "text": "6.0"
        }
    },
    "10244": {
        "estimated": {
            "value": 15.5,
            "text": "15.5"
        },
        "completed": {
            "value": 7.5,
            "text": "7.5"
        }
    },
    "10182": {
        "estimated": {
            "value": 12.0,
            "text": "12.0"
        },
        "completed": {
            "value": 10.0,
            "text": "10.0"
        }
    },
    "10391": {
        "estimated": {
            "value": 16.0,
            "text": "16.0"
        },
        "completed": {
            "value": 3.0,
            "text": "3.0"
        }
    },
    "10183": {
        "estimated": {
            "value": 12.0,
            "text": "12.0"
        },
        "completed": {
            "value": 7.0,
            "text": "7.0"
        }
    },
    "10123": {
        "estimated": {
            "value": 11.5,
            "text": "11.5"
        },
        "completed": {
            "value": 5.5,
            "text": "5.5"
        }
    },
    "10447": {
        "estimated": {
            "value": 7.0,
            "text": "7.0"
        },
        "completed": {
            "value": 3.0,
            "text": "3.0"
        }
    }
}
}

and my job in Talend is tRESTClient --> tExtractJSONFields --> tLogRow

currently i get either this output with this Loop Jsonpath query("$.sprints"):

First scenario:

first scenario

or this output with this Loop Jsonpath query("$"):

Second scenario:

second scenario

how can i combine them AND get the values: estimated & completed in rows instead of an array... please keep in mind i am new to JAVA/Talend/API..

Thanks in advance :)

tobi6
  • 8,033
  • 6
  • 26
  • 41
StackedOverflow
  • 21
  • 1
  • 8
  • JSON can be tricky. You might have a look at the tJSONNormalize component available in the Talend Exchange. It extracts JSON into a series of key/value pairs that can , in certain circumstances, be a lot easier to work with than pure JSON. https://exchange.talend.com/#marketplacesearch:gallery=marketplace%252F1&ob=releaseDate&o=0&c=20&d=true&s=tJsonNormalize&f=&tf=&bf=&cat=&title=Search%2520Results – BRM Oct 07 '16 at 14:31
  • thank you @BRM for the response, so i am using the tJSONNormalize now but i am not sure how to work with the output i get from it...i would like to get the values that appear after the velocityStatEntries. so the first one would be: 10516, then 10244 etc. I now get this (short version): – StackedOverflow Oct 10 '16 at 07:45
  • sprints>0|id|10516 sprints>0|sequence|10516 sprints>0|name|SP121 - BRK relief sprints>0|state|CLOSED sprints>0|linkedPagesCount|0 velocityStatEntries>10516>estimated|value|10.0 velocityStatEntries>10516>estimated|text|10.0 velocityStatEntries>10516>completed|value|7.5 velocityStatEntries>10516>completed|text|7.5 – StackedOverflow Oct 10 '16 at 07:46
  • Have you tried playing with the start path in tJSONNormalize? It can be helpful to use one of the many JSON parsers that can be found online to help figure out what start path you should use. – BRM Oct 11 '16 at 14:16
  • i have tried it but it did not help me a lot, do you have any suggestions on what start path i should use given the information above? also see this question @BRM, it is about almost the same thing. http://stackoverflow.com/questions/39958857/extract-leaves-from-json-file-with-jsonpath – StackedOverflow Oct 11 '16 at 15:15
  • If you have exhausted all your options with regards to parsing the flow as JSON it might be time to look at other ways of parsing it. I had some success with one job by doing a bunch of string substitutions on the incoming flow (you can use tReplace for that) to get the string down to a form that can be parsed as a delimited string, or with the tNormalize component. Its not very robust, but it can work. You could also parsing with regex if you are good at that. – BRM Oct 13 '16 at 18:08

0 Answers0