Page 1 of 1

JsonExtract could not read elements having SPACE

Posted: Tue Jun 05, 2018 6:34 am
by madan_clover3
Json data :

Code: Select all

{
  "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 :

Code: Select all

<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.

Re: JsonExtract could not read elements having SPACE

Posted: Tue Jun 12, 2018 7:27 am
by bartonv
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,

Re: JsonExtract could not read elements having SPACE

Posted: Fri Jun 15, 2018 6:44 am
by madan_clover3
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

Re: JsonExtract could not read elements having SPACE

Posted: Wed Jun 20, 2018 8:14 am
by bartonv
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,

Re: JsonExtract could not read elements having SPACE

Posted: Sun Dec 02, 2018 7:15 am
by madan_clover3
What if json source file contains two elements like 'abc xyz'(with space) and 'abc_xyz'(with underscore) and these 2 source elements are mapped to 2 different target elements. How it behaves in the above case, please clarify.

Re: JsonExtract could not read elements having SPACE

Posted: Tue Dec 11, 2018 3:56 pm
by bartonv
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

Re: JsonExtract could not read elements having SPACE

Posted: Wed Jan 30, 2019 6:09 am
by kaduswapnali456
Great topic! Excellent answers. Got solution. Thank you for the answers.