Unable to parse json data
sharmar6
New Altair Community Member
Hi, I am trying to fetch data from a API using "get_webpage". The website respods with a json array. No I am not able to use "cut document" to get specific values from the response by writing json queries.
I use the query : $.result.accountStatistics[0].
<?xml version="1.0" encoding="UTF-8"?><process version="7.5.001">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="7.5.001" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="web:get_webpage" compatibility="7.3.000" expanded="true" height="68" name="Twitter" width="90" x="45" y="34">
<parameter key="url" value="https://api.crowdtangle.com/leaderboard?token=JBLSJqnUcy07wvBTilMN41fiY8jyfnQ6onJeG7Kc&listId=344557 "/>
<parameter key="accept_cookies" value="all"/>
<list key="query_parameters"/>
<list key="request_properties"/>
</operator>
<operator activated="true" class="text:cut_document" compatibility="7.5.000" expanded="true" height="68" name="Cut Document" width="90" x="246" y="34">
<parameter key="query_type" value="JsonPath"/>
<list key="string_machting_queries"/>
<list key="regular_expression_queries"/>
<list key="regular_region_queries"/>
<list key="xpath_queries"/>
<list key="namespaces"/>
<list key="index_queries"/>
<list key="jsonpath_queries">
<parameter key="TotalIntercationCount" value="$.result.accountStatistics[0].summary.totalInteractionCount"/>
</list>
<process expanded="true">
<connect from_port="segment" to_port="document 1"/>
<portSpacing port="source_segment" spacing="0"/>
<portSpacing port="sink_document 1" spacing="0"/>
<portSpacing port="sink_document 2" spacing="0"/>
</process>
</operator>
<connect from_op="Twitter" from_port="output" to_op="Cut Document" to_port="document"/>
<connect from_op="Cut Document" from_port="documents" to_port="result 1"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
</process>
</operator>
</process>
Json response :
{
"status":200,
"result":{
"accountStatistics":[
{
"account":{
"id":1653853,
"platform":"Twitter" ,
"platformId":"331036671"
},
"summary":{
"totalInteractionCount" :888,
"postCount":6,
"interactionRate" :0.5536642849126482 ,
"likeCount":625,
"retweetCount":263
},
"breakdown":{
"native_video":{
"totalInteractionCount" :77,
"postCount":1 ,
"interactionRate" :0.28825996,
"likeCount":45 ,
"retweetCount" :32
},
"link":{
"totalInteractionCount" :195,
"postCount":3 ,
"interactionRate" :0.24316337,
"likeCount":126 ,
"retweetCount" :69
},
"tweet":{
"totalInteractionCount" :616,
"postCount":2 ,
"interactionRate" :1.15303983,
"likeCount":454 ,
"retweetCount" :162
}
},
"subscriberData":{
"initialCount":26712 ,
"finalCount":26750
}
},
{
"account":{
"id":76760,
"platform":"Twitter" ,
"platformId":"136250134"
},
"summary":{
"totalInteractionCount" :125,
"postCount":3,
"interactionRate" :0.10868700792619887 ,
"likeCount":91,
"retweetCount":34
},
"breakdown":{
"photo":{
"totalInteractionCount" :75,
"postCount":2 ,
"interactionRate" :0.09940621,
"likeCount":49 ,
"retweetCount" :26
},
"tweet":{
"totalInteractionCount" :50,
"postCount":1 ,
"interactionRate" :0.13254162,
"likeCount":42 ,
"retweetCount" :8
}
},
"subscriberData":{
"initialCount":37724 ,
"finalCount":37722
}
},
{
"account":{
"id":1545626,
"platform":"Twitter" ,
"platformId":"780606002"
},
"summary":{
"totalInteractionCount" :15,
"postCount":2,
"interactionRate" :0.10738666871212701 ,
"likeCount":10,
"retweetCount":5
},
"breakdown":{
"link":{
"totalInteractionCount" :15,
"postCount":2 ,
"interactionRate" :0.11508363,
"likeCount":10 ,
"retweetCount" :5
}
},
"subscriberData":{
"initialCount":6517 ,
"finalCount":6520
}
},
{
"account":{
"id":2042715,
"platform":"Twitter" ,
"platformId":"201886840"
},
"summary":{
"totalInteractionCount" :10,
"postCount":1,
"interactionRate" :0.3308519437551696 ,
"likeCount":8,
"retweetCount":2
},
"breakdown":{
"photo":{
"totalInteractionCount" :10,
"postCount":1 ,
"interactionRate" :0.33090668,
"likeCount":8 ,
"retweetCount" :2
}
},
"subscriberData":{
"initialCount":3022 ,
"finalCount":3023
}
},
{
"account":{
"id":2236790,
"platform":"Twitter" ,
"platformId":"847604654334554112"
},
"summary":{
},
"breakdown":{
},
"subscriberData":{
"initialCount":297 ,
"finalCount":299
}
}
],
"pagination":{
}
}
}
"status":200,
"result":{
"accountStatistics":[
{
"account":{
"id":1653853,
"platform":"
"platformId":"
},
"summary":{
"
"postCount":6,
"
"likeCount":625
"retweetCount":
},
"breakdown":{
"native_video":
"
"postCount":
"
"likeCount":
"
},
"link":{
"
"postCount":
"
"likeCount":
"
},
"tweet":{
"
"postCount":
"
"likeCount":
"
}
},
"subscriberData":{
"initialCount":
"finalCount":26
}
},
{
"account":{
"id":76760,
"platform":"
"platformId":"
},
"summary":{
"
"postCount":3,
"
"likeCount":91,
"retweetCount":
},
"breakdown":{
"photo":{
"
"postCount":
"
"likeCount":
"
},
"tweet":{
"
"postCount":
"
"likeCount":
"
}
},
"subscriberData":{
"initialCount":
"finalCount":37
}
},
{
"account":{
"id":1545626,
"platform":"
"platformId":"
},
"summary":{
"
"postCount":2,
"
"likeCount":10,
"retweetCount":
},
"breakdown":{
"link":{
"
"postCount":
"
"likeCount":
"
}
},
"subscriberData":{
"initialCount":
"finalCount":65
}
},
{
"account":{
"id":2042715,
"platform":"
"platformId":"
},
"summary":{
"
"postCount":1,
"
"likeCount":8,
"retweetCount":
},
"breakdown":{
"photo":{
"
"postCount":
"
"likeCount":
"
}
},
"subscriberData":{
"initialCount":
"finalCount":30
}
},
{
"account":{
"id":2236790,
"platform":"
"platformId":"
},
"summary":{
},
"breakdown":{
},
"subscriberData":{
"initialCount":
"finalCount":29
}
}
],
"pagination":{
}
}
}
Please help.
0
Answers
-
Seems it is expecting a double dot, this works :
$.result.accountStatistics[0]..
summary.totalInteractionCount As an alternative you can also just cut on the accountStatistics and then use a json to data component as below example, this makes it a bit more easy to work with the full set of data.
<?xml version="1.0" encoding="UTF-8"?><process version="7.6.001">
<context>
<input/>
<output/>
<macros/>
</context>
<operator activated="true" class="process" compatibility="7.6.001" expanded="true" name="Process">
<process expanded="true">
<operator activated="true" class="text:create_document" compatibility="7.5.000" expanded="true" height="68" name="Create Document" width="90" x="179" y="85">
<parameter key="text" value="{ 	"status": 200, 	"result": { 		"accountStatistics": [ 			{ 				"account": { 					"id": 1653853, 					"platform": "Twitter", 					"platformId": "331036671" 				}, 				"summary": { 					"totalInteractionCount": 888, 					"postCount": 6, 					"interactionRate": 0.5536642849126482, 					"likeCount": 625, 					"retweetCount": 263 				}, 				"breakdown": { 					"native_video": { 						"totalInteractionCount": 77, 						"postCount": 1, 						"interactionRate": 0.28825996, 						"likeCount": 45, 						"retweetCount": 32 					}, 					"link": { 						"totalInteractionCount": 195, 						"postCount": 3, 						"interactionRate": 0.24316337, 						"likeCount": 126, 						"retweetCount": 69 					}, 					"tweet": { 						"totalInteractionCount": 616, 						"postCount": 2, 						"interactionRate": 1.15303983, 						"likeCount": 454, 						"retweetCount": 162 					} 				}, 				"subscriberData": { 					"initialCount": 26712, 					"finalCount": 26750 				} 			}, { 				"account": { 					"id": 76760, 					"platform": "Twitter", 					"platformId": "136250134" 				}, 				"summary": { 					"totalInteractionCount": 125, 					"postCount": 3, 					"interactionRate": 0.10868700792619887, 					"likeCount": 91, 					"retweetCount": 34 				}, 				"breakdown": { 					"photo": { 						"totalInteractionCount": 75, 						"postCount": 2, 						"interactionRate": 0.09940621, 						"likeCount": 49, 						"retweetCount": 26 					}, 					"tweet": { 						"totalInteractionCount": 50, 						"postCount": 1, 						"interactionRate": 0.13254162, 						"likeCount": 42, 						"retweetCount": 8 					} 				}, 				"subscriberData": { 					"initialCount": 37724, 					"finalCount": 37722 				} 			}, { 				"account": { 					"id": 1545626, 					"platform": "Twitter", 					"platformId": "780606002" 				}, 				"summary": { 					"totalInteractionCount": 15, 					"postCount": 2, 					"interactionRate": 0.10738666871212701, 					"likeCount": 10, 					"retweetCount": 5 				}, 				"breakdown": { 					"link": { 						"totalInteractionCount": 15, 						"postCount": 2, 						"interactionRate": 0.11508363, 						"likeCount": 10, 						"retweetCount": 5 					} 				}, 				"subscriberData": { 					"initialCount": 6517, 					"finalCount": 6520 				} 			}, { 				"account": { 					"id": 2042715, 					"platform": "Twitter", 					"platformId": "201886840" 				}, 				"summary": { 					"totalInteractionCount": 10, 					"postCount": 1, 					"interactionRate": 0.3308519437551696, 					"likeCount": 8, 					"retweetCount": 2 				}, 				"breakdown": { 					"photo": { 						"totalInteractionCount": 10, 						"postCount": 1, 						"interactionRate": 0.33090668, 						"likeCount": 8, 						"retweetCount": 2 					} 				}, 				"subscriberData": { 					"initialCount": 3022, 					"finalCount": 3023 				} 			}, { 				"account": { 					"id": 2236790, 					"platform": "Twitter", 					"platformId": "847604654334554112" 				}, 				"summary": {}, 				"breakdown": {}, 				"subscriberData": { 					"initialCount": 297, 					"finalCount": 299 				} 			} 		], 		"pagination": {} 	} }"/>
</operator>
<operator activated="true" class="text:cut_document" compatibility="7.5.000" expanded="true" height="68" name="Cut Document" width="90" x="313" y="85">
<parameter key="query_type" value="JsonPath"/>
<list key="string_machting_queries"/>
<list key="regular_expression_queries"/>
<list key="regular_region_queries"/>
<list key="xpath_queries"/>
<list key="namespaces"/>
<list key="index_queries"/>
<list key="jsonpath_queries">
<parameter key="test" value="$.result.accountStatistics"/>
</list>
<process expanded="true">
<connect from_port="segment" to_port="document 1"/>
<portSpacing port="source_segment" spacing="0"/>
<portSpacing port="sink_document 1" spacing="0"/>
<portSpacing port="sink_document 2" spacing="0"/>
</process>
</operator>
<operator activated="true" class="text:json_to_data" compatibility="7.5.000" expanded="true" height="82" name="JSON To Data" width="90" x="447" y="85"/>
<connect from_op="Create Document" from_port="output" to_op="Cut Document" to_port="document"/>
<connect from_op="Cut Document" from_port="documents" to_op="JSON To Data" to_port="documents 1"/>
<connect from_op="JSON To Data" from_port="example set" to_port="result 1"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
</process>
</operator>
</process>1
