JsonExtract could not read json starting with array

Discussion on developing CloverETL engine, transformation components etc.

madan_clover3
Posts: 20
Joined: Tue Jun 10, 2014 10:39 am

JsonExtract could not read json starting with array

Postby madan_clover3 » Thu May 03, 2018 8:30 am

If json is starting with array, JsonExtract is not reading any data.

Json starting with array :

Code: Select all

   [
   {
  "id": "1",
  "name": "emp1",
  "addresses": [
    {
      "area": "area1",
      "city": "city1",
      "state": "state1",
      "pin": "pinxyz"
    }
  ],
  "phoneNumbers" : [
    {
      "type":"home",
      "number": "212 555-1234"
    },
    {
      "type": "fax",
      "number": "646 555-4567"
    }
  ]
}
]


and extraction mapping is :

Code: Select all

   <mappings>
   <Mapping cloverFields="id;name" element="json_object" outPort="0" sequenceField="json_object_seq" sequenceId="InternalSequence" xmlFields="{}id;{}name">
      <Mapping cloverFields="area;city;state;pin" element="addresses" generatedKey="parentId_json_object" outPort="1" parentKey="json_object_seq" sequenceField="addresses_seq" sequenceId="InternalSequence" xmlFields="{}area;{}city;{}state;{}pin"/>
      <Mapping cloverFields="type;number" element="phoneNumbers" generatedKey="parentId_json_object" outPort="2" parentKey="json_object_seq" sequenceField="phoneNumbers_seq" sequenceId="InternalSequence" xmlFields="{}type;{}number"/>
   </Mapping>
</mappings>


Records emitted from JsonExtract are 0 from all output ports. Where as if json comes as object without array, it is working as expected.

Child element arrays are working good, problem with root level array.

Json without array :

Code: Select all

   {
  "id": "1",
  "name": "emp1",
  "addresses": [
    {
      "area": "area1",
      "city": "city1",
      "state": "state1",
      "pin": "pinxyz"
    }
  ],
  "phoneNumbers" : [
    {
      "type":"home",
      "number": "212 555-1234"
    },
    {
      "type": "fax",
      "number": "646 555-4567"
    }
  ]
}



Expecting this to be handled by JsonExtract. Please suggest how to handle above scenario.

bartonv
Posts: 79
Joined: Wed May 03, 2017 12:10 pm

Re: JsonExtract could not read json starting with array

Postby bartonv » Wed May 09, 2018 1:53 pm

Hi Madan_clover3,
from looking at the snippet of your JSON input data and the mapping, I am a little bit on the fence as to where the mapping actually came from. From the first glance, I noticed that root element mappings should, in fact, be Mappings (note uppercase M) and the first element should not be json_object but json_array instead. Did you use the visual mapping editor in JSONExtract?
Attached is a project containing a graph where using the visual mapping editor is used to pull data from both of your JSON snippets. The data gets extracted successfully from both. Please review it and let us know if you have any questions.
Kind regards,
Attachments
JSONextract.zip
(6.01 KiB) Downloaded 12 times
---
Vladimir Barton
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support

madan_clover3
Posts: 20
Joined: Tue Jun 10, 2014 10:39 am

Re: JsonExtract could not read json starting with array

Postby madan_clover3 » Thu May 17, 2018 10:05 am

Hi,

Thanks for response. After using 'json_array' it is working as expected. Regarding mapping - not used any editor. I am using clover open source engine in my java application, generated mapping using custom code. I could not find any documentation about 'json_array'. Your response is helpful to me. Please suggest if there is any java specific documentation.

And extraction works with 'mappings' (small m) as well.

bartonv
Posts: 79
Joined: Wed May 03, 2017 12:10 pm

Re: JsonExtract could not read json starting with array

Postby bartonv » Tue May 22, 2018 11:43 am

Hi Madan_clover3,
whether you should select ‘json_array’ or ‘json_object’ in the mapping depends on the actual input data. In this case, it depends on whether the root element in the JSON structure is an object or an array. This difference is shown in the example I have attached in my previous post (note your 2 types of input data and the mapping definition that together demonstrate the difference between root as json_object and json_array). As for the documentation, this topic is dealt with within the JSONReader component documentation as JSONReader requires manual mapping definition whereas JSONExtract is primarily designed to facilitate visual mapping editing in CloverETL Designer. Worth noting is also that we do not have a Java-specific documentation, however, we do have a CloverETL release-specific documentation:
Kind regards,
---
Vladimir Barton
CloverCARE Support
CloverETL | Rapid Data Integration

Visit us online at http://www.cloveretl.com
How to speed up communication with CloverCARE support