Jpath query using macro
MavenHi everyone,
Please help me in using "Generate Attribute" for extracting values from a json response from a URL. I am retrieving json resonse from a URL which has date wise value starting from "2017-01-01". I need to extract latest value (previous day value).
I have a macro for replacing the "day of the year" in json querry. I inserted the macro in the JSON query, but its giving error.
The query : [timeline\[eval(%{no_Of_Days})\].station_values\[0\].value]
The XML of the process is :
<?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="generate_macro" compatibility="7.5.001" expanded="true" height="82" name="Generate Macro" width="90" x="179" y="34">
<list key="function_descriptions">
<parameter key="m_no_Of_Days" value="floor(date_diff(date_parse_custom("2017-01-01","yyyy-MM-dd"),date_parse_custom(date_str_custom(date_now(),"yyyy-MM-dd"),"yyyy-MM-dd"))/86400000)"/>
<parameter key="m_endDate" value="date_str_custom(date_add(date_now(), -1, DATE_UNIT_DAY), "MM%2Fdd%2FYYYY")"/>
<parameter key="no_Of_Days" value="284"/>
</list>
</operator>
<operator activated="true" class="web:get_webpage" compatibility="7.3.000" expanded="true" height="68" name="TV Mentions" width="90" x="179" y="136">
<parameter key="url" value="http://television.gdeltproject.org/cgi-bin/iatv_ftxtsearch/iatv_ftxtsearch?primary_keyword=Gillespie%2C+ed&context_keywords=&filter_network=NATIONAL&filter_timespan=CUSTOM&filter_timespan_custom_start=01%2F01%2F2017&filter_timespan_custom_end=%{m_endDate}&filter_displayas=RAW&filter_combineseparate=COMBINE&filter_outputtype=JSON#searchbox "/>
<parameter key="accept_cookies" value="all"/>
<list key="query_parameters"/>
<list key="request_properties"/>
</operator>
<operator activated="true" class="delay" compatibility="7.5.001" expanded="true" height="103" name="Delay" width="90" x="447" y="34"/>
<operator activated="true" class="text:json_to_data" compatibility="7.5.000" expanded="true" height="82" name="JSON To Data (2)" width="90" x="648" y="34"/>
<operator activated="true" class="generate_attributes" compatibility="7.5.001" expanded="true" height="82" name="Generate Attributes" width="90" x="849" y="34">
<list key="function_descriptions">
<parameter key="value" value="[timeline\[eval(%{no_Of_Days})\].station_values\[0\].value]"/>
</list>
</operator>
<connect from_port="input 1" to_op="Generate Macro" to_port="through 1"/>
<connect from_op="Generate Macro" from_port="through 1" to_op="Delay" to_port="through 1"/>
<connect from_op="TV Mentions" from_port="output" to_op="Delay" to_port="through 2"/>
<connect from_op="Delay" from_port="through 1" to_op="JSON To Data (2)" to_port="documents 1"/>
<connect from_op="JSON To Data (2)" from_port="example set" to_op="Generate Attributes" to_port="example set input"/>
<connect from_op="Generate Attributes" from_port="example set output" to_port="result 1"/>
<portSpacing port="source_input 1" spacing="0"/>
<portSpacing port="source_input 2" spacing="0"/>
<portSpacing port="sink_result 1" spacing="0"/>
<portSpacing port="sink_result 2" spacing="0"/>
</process>
</operator>
</process>
Answers
Hi everyone,
please, I need your help in this problem that I am struggling with. I need to come up with an expression to get the value from a API (JSON) where I use "day of the year" as a macro in a expression in "Generate Attribute".
Please help.
With what exactly are you struggling? Could you post what you have done so far and explain what is not working as intended? The xml process above is somehow corrupted, as it contains several <process> tags.
Thanks for replying.
I have to retrieve latest data everyday from a JSON API that has an attribute data from the starting day of the year ( 01-01-2017) to present day. The JSON path has day of the year as number ( i.e 01-01-2017 as '1' and 12-31-2017 as '365'). So I need to have the latest day number in the querry to get the present day value from the JSON. I used a macro for getting the latest day number and that is working fine. Problem is, I am not able to use this macro in the querry to generate attribute
--> [timeline\[eval(%{no_Of_Days})\].station_values\[0\].value]. ( as seen in the attached pic)
<operator activated="true" class="generate_attributes" compatibility="7.5.001" expanded="true" height="82" name="Generate Attributes" width="90" x="581" y="136">
<list key="function_descriptions">
<parameter key="value" value="[timeline\[eval(%{no_Of_Days})\].station_values\[0\].value]"/>
</list>
This the macro that I am using :
floor(date_diff(date_parse_custom("2017-01-01","yyyy-MM-dd"),date_parse_custom(date_str_custom(date_now(),"yyyy-MM-dd"),"yyyy-MM-dd"))/86400000)
Hope I am able to explain this time.
Thanks.
hello @sharmar6 - we'd be happy to help here but please post your complete XML code using the </> tool (see this article for help: https://community.rapidminer.com/t5/RapidMiner-Studio-Knowledge-Base/How-can-I-share-processes-without-RapidMiner-Server/ta-p/37047).
Scott
Ok, I believe I see the issue. Whenever you put something into square brackets on the function expression side, RM thinks that this is an attribute name. Hence the error message.
What I believe you are trying to do is to "stitch" together several strings and your macro. In order to do this, you would want to use the "concat" function expression. Hence your function expression should look like this:
concat("timeline\[",%{no_Of_Days},"\].station_values\[0\].value]")
It is important that you do not have any whitespace between the comma separator, otherwise your concatenated string won't work as intended.
Thanks a lot for replying.
I am trying to make the JSON path querry .. which looks like
: [timeline\[294\].station_values\[0\].value]
and replace the day number = 294 with the macro %{no_Of_Days}.
CONCAT function gave an error when I tried your suggestion.
hello @sharmar6 - ok I spent some time with your process. I think the issue is earlier on. The "JSON to Data operator" does not like the use of hard brackets [] in its JSON expressions, only curly brackets {}. Furthermore there is some dirty data coming back that the JSON parser does not like: characters such as { } [ ] ' " in your text. You will need to clean up all those characters before feeding it to the JSON to Data operator.
IMO when you are in this mess, it is often easier in RapidMiner to move to XML as then you can use the Read XML operator rather than JSON (there is no equivalent "Read JSON" operator at this moment). Something like this:
Scott
@sgenzer is right. Also, I forgot to escape the "\" symbol in my answer above. The correct syntax using concat would be:
concat("timeline","\\","[",%{no_Of_Days},"\\","].station_values","\\","[0","\\","].value]")
However, I am not longer sure, if you used the "\" to escape the squared brackets, or if they should form part of your expression.
Thank you very much. I also solved the problem. I was generating an attribute from the JSON response. But now I can get the value by just selecting the attribute from the JSON to DATA operator using "Select Attribute" operator and using the above jpath query as the name of the attribute.
XML code attached.
Thanks again.