使用JSONpath从JSON文件中提取叶子 [英] Extract leaves from JSON file with JSONpath
问题描述
我有一个来自REST API的JSON输出，输出看起来像这样:
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"
}
}
}}
我想从VelocityStatEntries中提取叶子和叶子内部的信息.
i would like to extract the leaves AND the info inside the leaves from velocityStatEntries.
所以预期的输出将是这样:
so the expected output would be this:
sprint_id |估计完全的 10516 | 10.0 | 7.5 10244 | 15.5 | 7.5 等
sprint_id | estimated | completed 10516 | 10.0 | 7.5 10244 | 15.5 | 7.5 etc.
奇怪的是，当我尝试通过此在线JSONpath测试器(jsonpath.curiousconcept.com/)执行此操作时，我通过类似"$ .velocityStatEntries"的查询获得了预期的结果.我明白了:
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"
}
}}]
但是因为我使用的是Talend Open Studio，所以我必须输入Loop Jsonpath查询，然后指定映射.有谁知道如何在Talend中解决此问题?我正在使用tExtractJSONFields组件
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
一些其他屏幕截图，以获取更多信息
some additional screenshots for extra information
工作1:
输出1:
无法发布更多屏幕，因为我没有足够的声誉点...:(
Cannot post more screens because i do not have enough reputation points...:(
推荐答案
在 jsonpath.com 上进行测试您在问题中显示的查询$.velocityStatEntries.会生成可映射的数据:
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"
使用在注释$.velocityStatEntries[*]中显示的查询测试相同的源数据将导致没有要映射的数据:
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.
编辑
您似乎很亲近.提供更多信息后，建议您检查查询$.velocityStatEntries，并提取字段[0]，estimated.value和completed.value.
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.
我对[0]不太确定(我已经在此处使用了不同的JSON模式进行了建议) .这是因为JSONPath不能与本身就是ID的元素配合使用. XMLPath根本无法解释这一点，因此您可以检查我的其他答案以获取更多参考，以获取有关如何从元素描述符(即ID)中收集数据的进一步参考.
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.
这篇关于使用JSONpath从JSON文件中提取叶子的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
