Json data :
Extraction mapping :
JsonExtract could not read "phone numbers" data. Suggestions please.
{
"id": "1",
"name": "emp1",
"addresses": [
{
"area": "area1",
"city": "city1",
"state": "state1",
"pin": "pinxyz"
}
],
"phone numbers": [
{
"type": "home",
"number": "212 555-1234"
},
{
"type": "fax",
"number": "646 555-4567"
}
]
}Extraction mapping :
<Mappings>
<Mapping cloverFields="id;name" element="json_object" outPort="0" xmlFields="{}id;{}name">
<Mapping cloverFields="area;city;state;pin" element="addresses" outPort="1" xmlFields="{}area;{}city;{}state;{}pin"/>
<Mapping cloverFields="type;number" element="phone numbers" outPort="2" xmlFields="{}type;{}number"/>
</Mapping>
</appings>JsonExtract could not read "phone numbers" data. Suggestions please.
-
Hello Madan_clover3,
in order to resolve the issue, please replace “phone numbers” in your mapping with “phone_numbers”. The reason why the underscore needs to replace the space symbol in the mapping is that Clover internally converts JSON into XML and the space symbol is not a valid character in XML. When using the visual mapper within the JSONExtract component, it gets replaced automatically.
Kind regards, -
please replace “phone numbers” in your mapping with “phone_numbers” : 'phone numbers' represents element in json data file. Replacing in mapping file is enough? We might need to change in data file as well. Data file is not in our control, it is user uploaded file. Yeah space is invalid in xml elements, but is valid in json elements. So Can I conclude JsonExtract has a limitation of reading elements with space? Please update -
Hello Madan_clover3,
yes, replacing the space symbol with an underscore in the mapping is enough for your graph to work properly. In other words, no changes need to take place in the input file. As you rightly said, the space symbol is indeed a valid character in JSON. However, due to the way how CloverETL is currently designed to work (it internally converts JSON into XML before the parsing itself) the mapping needs to fulfill the XML standards.
As I mentioned in my previous post, JSONExtract is intended for usage as a visual mapping tool in the first place and the underscore replacement happens automatically. So to answer your last question, JSONExtract does not have a limitation in reading elements with spaces, it just requires to avoid spaces in the mapping which is not a concern for most users.
Best, -
Hello Madan_clover3,
in such a scenario, the JSONExtract component would not be a good candidate for the parsing. Due to the fact, that the space character " " is automatically replaced by the underscore "_" both elements would fuse into a single one ("abc_xyz") and their respective values would be mixed. I have logged this issue into our JIRA so that it gets reviewed and addressed by our development team.
A workaround solution to this problem is to utilize the JSONReader component where both space and underscore characters are replaced by escaped character sequences. Attached is an example graph where I have used a slightly modified version of your sample input file.
Kind regards,
Vladi
Please sign in to leave a comment.
Comments 6