Extract leaves from JSON file with JSONpath

Question

I have a JSON output from an REST API and the output looks like this:

{
"sprints": [{
    "id": 10516,
    "sequence": 10516,
    "name": "SP121 - BRK relief",
    "state": "CLOSED",
    "linkedPagesCount": 0
}, {
    "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
}],
"velocityStatEntries": {
    "10516": {
        "estimated": {
            "value": 10.0,
            "text": "10.0"
        },
        "completed": {
            "value": 7.5,
            "text": "7.5"
        }
    },
    "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"
        }
    }
}}

i would like to extract the leaves AND the info inside the leaves from velocityStatEntries.

so the expected output would be this:

sprint_id | estimated | completed 10516 | 10.0 | 7.5 10244 | 15.5 | 7.5 etc.

the strange thing is when i try to do this thru this online JSONpath tester (jsonpath.curiousconcept.com/) i get the expected result with a query like this "$.velocityStatEntries." there i get this:

[   {  
  "10516":{  
     "estimated":{  
        "value":10,
        "text":"10.0"
     },
     "completed":{  
        "value":7.5,
        "text":"7.5"
     }
  },
  "10244":{  
     "estimated":{  
        "value":15.5,
        "text":"15.5"
     },
     "completed":{  
        "value":7.5,
        "text":"7.5"
     }
  },
  "10182":{  
     "estimated":{  
        "value":12,
        "text":"12.0"
     },
     "completed":{  
        "value":10,
        "text":"10.0"
     }
  },
  "10391":{  
     "estimated":{  
        "value":16,
        "text":"16.0"
     },
     "completed":{  
        "value":3,
        "text":"3.0"
     }
  },
  "10183":{  
     "estimated":{  
        "value":12,
        "text":"12.0"
     },
     "completed":{  
        "value":7,
        "text":"7.0"
     }
  },
  "10123":{  
     "estimated":{  
        "value":11.5,
        "text":"11.5"
     },
     "completed":{  
        "value":5.5,
        "text":"5.5"
     }
  },
  "10447":{  
     "estimated":{  
        "value":7,
        "text":"7.0"
     },
     "completed":{  
        "value":3,
        "text":"3.0"
     }
  }}]

but because i am using Talend Open Studio, i have to enter a Loop Jsonpath query and then specify the mapping. does anyone know how to fix this in Talend? im using the tExtractJSONFields component

EDIT: some additional screenshots for extra information

Job1:

Output1:

Cannot post more screens because i do not have enough reputation points...:(

Add two tExtractJSONFields, one for `sprints`, one for `velocityMapEntries`. Both with different loops: First one with loop over sprints, second one with loop over velocityMapEntries. Add tMap. Use one of the inputs as a lookup on the "id" field. Join in tMap. Process your data. — tobi6, Oct 10 '16 at 13:05
thanks for the response @tobi6 but i am already using two tExtractJSONFields to accomplish that. but where i am stuck is extracting the leaf value, so in this case that would be '10516' for the first one. when i use the loop '$.velocityStatEntries[*]' i do get al the values for 'estimated' and 'completed' but i never get back the leaf value... that is where my knowlegde about jsonpath ends. do you have any suggestions on what kind of loop i can use and what query to extract the leaf value? — StackedOverflow, Oct 10 '16 at 13:52

score 0 · Answer 1 · edited May 23 '17 at 12:00

0

Testing on jsonpath.com with the query $.velocityStatEntries. you showed in the question results in data which could be mapped:

'0' ...
  '10123' ...
    'estimated' ...
      'value' => "11.5"
      'text' => "11.5"
    'completed' ...
      'value' => "5.5"
      'text' => "5.5"
  '10182' ...
    'estimated' ...
      'value' => "12"
      'text' => "12.0"
    'completed' ...
      'value' => "10"
      'text' => "10.0"
  '10183' ...
    'estimated' ...
      'value' => "12"
      'text' => "12.0"
    'completed' ...
      'value' => "7"
      'text' => "7.0"

Testing the same source data with the query you showed in the comments $.velocityStatEntries[*] results in no data to map:

'0' ...
  'estimated' ...
    'value' => "11.5"
    'text' => "11.5"
  'completed' ...
    'value' => "5.5"
    'text' => "5.5"
'1' ...
  'estimated' ...
    'value' => "12"
    'text' => "12.0"
  'completed' ...
    'value' => "10"
    'text' => "10.0"
'2' ...
  'estimated' ...
    'value' => "12"
    'text' => "12.0"
  'completed' ...
    'value' => "7"
    'text' => "7.0"

I suggest checking your query again and using the first one.

EDIT

You seem very close. After more information is available I suggest you check the query $.velocityStatEntries and extract the fields [0], estimated.value and completed.value.

I am not too sure about the [0] (I have suggested this with a little different JSON schema here). This is because JSONPath does not work well with elements which are IDs for themselves. XMLPath can't interpret this at all, so you might check my other answer for further reference in how to gather data from element descriptors which are IDs.

edited May 23 '17 at 12:00

Community

1
1

answered Oct 10 '16 at 14:00

tobi6

8,033
6
26
41

i already came this far myself, so if you could please help me with the solution for Talend that would be very nice... like i have mentioned in my question, i have the right loop i guess, but i don't know how to extract the leaf in JSONpath query inside Talend. In the JSONpath testers i can get the right data, but that is because there is no looping there, you can jut fill in the query (path)..but how does this work in Talend? – StackedOverflow Oct 11 '16 at 07:46
I cannot help right now since it is not clear what works and what does not when reading your question. What is your current state? What is your actual question? How does your job look like? Which query statement *do* you use? – tobi6 Oct 11 '16 at 13:00
I am sorry the question was not clear enough, i have eddited it. unfortunately i cannot post more links so i will do it here: [3]: https://i.stack.imgur.com/6ntSY.png [4]: https://i.stack.imgur.com/xaJBd.png [5]: https://i.stack.imgur.com/XMkeb.png My question is how can i get the following output: headers: ID | estimated | completed with the values: 10516 | 10 | 7.5 etc. – StackedOverflow Oct 11 '16 at 15:02
that won't work @tobi6 because it is not an array, '[0]' only works with arrays(yes, i tried). I am still stuck and getting a bit desperate and doubting if this is even possible to achieve??? can this be done with any kind of java? (note that i am absolutely not experienced in java) – StackedOverflow Oct 13 '16 at 08:15
I think it is very hard to achieve with standard components since JSONPath *needs* first a label, then a value. If the label is the value itself, you have to trick with adding brackets to fake an array. If you want to create a method with Java you should ask a new question for that specifically. I suppose it could be done. – tobi6 Oct 13 '16 at 08:20

Extract leaves from JSON file with JSONpath

1 Answers1

Linked