I am setting up a REST call (from alphavantage.co) which returns a JSON object whose key names include the ':' character. The keys are enclosed in quotes so this is (apparently?) allowed. Here is an abridged version of the snippet: { "Meta Data": { "Information": "US Sector Performance (realtime & historical)", "Last Refreshed": "04:20 PM ET 11/17/2017" }, "Rank A: Real-Time Performance": { "Telecommunication Services": "0.65%", "Consumer Discretionary": "0.48%", "Energy": "0.43%", "Materials": "0.11%", "Financials": "-0.13%", "Health Care": "-0.39%", "Consumer Staples": "-0.41%", "Industrials": "-0.48%", "Real Estate": "-0.56%", "Information Technology": "-0.67%", "Utilities": "-0.74%" }, "Rank B: 1 Day Performance": { "Telecommunication Services": "0.64%", "Consumer Discretionary": "0.48%", "Energy": "0.43%", "Materials": "0.11%", "Financials": "-0.13%", "Health Care": "-0.39%", "Consumer Staples": "-0.41%", "Industrials": "-0.48%", "Real Estate": "-0.56%", "Information Technology": "-0.67%", "Utilities": "-0.74%" } } When I create a JSON snippet in Mendix (v7.5.1), the "prefix" to the keys containing quotes is removed. For example: "Rank B: 1 Day Performance" becomes " 1 Day Performance" (including the leading space). The call correctly returns the root and meta-data objects but the other objects (Rank A, Rank B, etc.) are not returned. I assume this is because the key names do not match? Is this a bug or an issue with JSON from provider? Any work around within Mendix? For example, is it possible to call a Microflow to rename the returned keys so that they match the JSON snippet? Is it possible to set up the import mapping "manually" (without a JSON snippet)? Thanks!
asked
George Wyner
1 answers
3
Hi George,
This looks like a fault on the provider side, because a colon (:) is seperating the name from it's values.
A name/value pair consists of a field name (in double quotes), followed by a colon, followed by a value
https://www.w3schools.com/js/js_json_syntax.asp
So I would talk to the provider and ask them to replace that specific colon (:) with another seperator so you can later replace that with the colon when processing each object. Updated see my EDIT below.
What you could do is retrieve the JSON string, replace the : with your for example a '-' and import the data, after importing replace the '-' again with a ':'. However you only want to replace the colons that are within the double quotes.
Good luck.
EDIT: Other JSON viewers can read the colon in the double quotes as text, so as Mendix could also process the data in the doubles quotes as text this could be reported as a bug or feature request to Mendix.