5 Replies Latest reply on Mar 20, 2021 6:36 AM by Nico Heinze

    How to parse the JSON using JTX?

    Mani Ar Seasoned Veteran

      Hi, I am not a Java expert and hence struggling to parse the JSON file in Informatica Power Center using JTX.

       

      I have the following JSON file <<I did not copy the entire file due to size>> and I have the following JS code in the JTX.

       

      {

         "took":6,

         "timed_out":false,

         "_shards":{

            "total":5,

            "successful":5,

            "skipped":0,

            "failed":0

         },

         "hits":{

            "total":{

               "value":593,

               "relation":"eq"

            },

            "max_score":1,

            "hits":[

               {

                  "_index":"store_data_1_product_1616054411",

                  "_type":"_doc",

                  "_id":"9",

                  "_score":1,

                  "_source":{

                     "attribute_set_id":4,

                     "created_at":"2020-07-03 19:59:38",

                     "updated_at":"2021-03-06 18:57:19",

                     "type_id":"simple",

                     "sku":"VP000024",

                     "id":9,

                     "tsk":1616054411,

                     "manufacturer":5476,

                     "status":1,

                     "visibility":4,

                     "tax_class_id":2,

                     "cannabis":1,

                     "sexual_wellness":0,

                     "pet_friendly":0,

                     "name":"Rainmaker ",

                     "image":"/c/l/cld_5f08a62f7416d_Chemistry-Rainmaker-VP000024-1.jpg",

                     "small_image":"/c/l/cld_5f08a62f7416d_Chemistry-Rainmaker-VP000024-1.jpg",

                     "thumbnail":"/c/l/cld_5f08a62f7416d_Chemistry-Rainmaker-VP000024-1.jpg",

                     "image_label":"product-images/VP000024/Chemistry-Rainmaker-VP000024-1",

                     "small_image_label":"product-images/VP000024/Chemistry-Rainmaker-VP000024-1",

                     "thumbnail_label":"product-images/VP000024/Chemistry-Rainmaker-VP000024-1",

                     "url_key":"vp000024",

                     "msrp_display_actual_price_type":"0",

                     "gift_message_available":false,

                     "gift_wrapping_available":false,

                     "is_returnable":true,

                     "swatch_image":"/c/l/cld_5f08a62f7416d_Chemistry-Rainmaker-VP000024-1.jpg",

                     "classification":"indica",

                     "treez_uom":"grams",

                     "price":30,

                     "thc_percentage":78.2,

                     "thc_mg_package":391,

                     "cbd_percentage":0.3,

                     "cbd_mg_package":2,

                     "treez_amount":0.5,

                     "description":"Chemistry brings small-batch craft cannabis to you in vape form, with a proprietary extraction process that aims to provide a vaping experience that is as close to the cured, dried cannabis flower as possible. This single-source, single-strain, whole-flower vape is extracted from cannabis sun-grown in Humboldt by Tina Gordon at Moon Made Farms. Today’s forecast? A deluge of sweet and skunky good vibes courtesy of Rainmaker. Wade into a mellow mood with this indica hybrid. Chemistry: Stay True to the Plant.",

                     "treez_product_id":"a2c98fcd-2fbd-4a3a-a9bd-404348796bf8",

                     "treez_category":"Cartridge",

                     "treez_sub_category":"Pod",

                     "path":"/brands/chemistry/rainmaker/",

       

      When I debug, I get the error: "jsonobject cannot be cast to class org.json.simple.jsonarray"

       

      here the InputFile is a input port containing the path of my JSON file.

       

      try {

       

       

      FileReader reader = new FileReader(InputFile);

       

      JSONParser jsonParser = new JSONParser();

      JSONObject jsonObject = (JSONObject) jsonParser.parse(reader);

       

       

      JSONObject jsonObject1 = new JSONObject();

      JSONArray skus = (JSONArray) jsonObject1.get("hits");

      //JSONArray skus = obj.getJSONArray("hits");

       

       

      for (int i=0; i < skus.size(); i++)

      {

      JSONObject skufields = (JSONObject)skus.get(i);

      String sku = (String) skufields.get("sku");

       

       

      String name = (String) skufields.get("name");

       

       

      String path = (String) skufields.get("path");

      String price = (String) skufields.get("price");

       

       

      sku=sku;

      name = name;

      path = path;

      price = price;

      generateRow();

      }

       

       

      JSONArray categories = (JSONArray) jsonObject.get("category");

       

       

      for (int i=0; i < categories.size(); i++)

      {

       

       

      JSONObject categoryfields = (JSONObject)categories.get(i);

       

       

      String categoryname = (String) categoryfields.get("name");

       

       

      String categorypos = (String) categoryfields.get("position");

       

       

       

       

      categoryname=categoryname ;

       

       

      position=categorypos;

       

       

      generateRow();

       

       

      }

       

       

          }

       

       

       

       

      catch (FileNotFoundException ex) {

                  ex.printStackTrace();

              } catch (IOException ex) {

                  ex.printStackTrace();

              } catch (ParseException ex) {

                  ex.printStackTrace();

              } catch (NullPointerException ex) {

                  ex.printStackTrace();

              }

       

      Any help to fix and read/parse my JSON file is highly appreciated.

        • 1. Re: How to parse the JSON using JTX?
          Nico Heinze Guru

          There are several issues with the code.

          First you have the following assignments before the first call to "generateRow()":

              sku=sku;

              name = name;

              path = path;

              price = price;

              generateRow();

           

          To me this looks as if you're trying to assign some output port values to themselves. That can't work. Input-only ports, output-only-ports, and Java variables all have to have different names.

           

          Second you do a very similar thing at the second call to generateRow():

              categoryname=categoryname;

              position=categorypos;

              generateRow();

           

          To me this looks as if you have at least six different output ports, namely sku, name, path, price, categoryname, and position. It is a very bad idea to only feed some of these ports and not do anything with the other ports; you should at least set the other ports to NULL using setNull().

           

          But that's not the main thing. The error message tells me (BTW I'm by no means a Java export) that the following code line cannot work as you're trying to do it:

              JSONArray skus = (JSONArray) jsonObject1.get("hits");

           

          The error is pretty logical from a technical point of view. Both JSONArray and JSONObject are classes in the package org.json, they are not "related" in the sense that one could be "cast" into another. And this code line above tries to exactly do this "translate" the result of jsonObect1.get() into a JSONArray.

           

          Honestly I don't understand much about the JSON stuff, so I can only guess (from the Javadoc for JSONObject) that you may want to try something like this:

              JSONArray skus = jsonObject1.getJSONArray( "hits");

          It may work.

           

          There are a few other JSONObject methods which can return a JSONArray, but whether you need any of these or getJSONArray() I can't tell as I don't understand much about this subject altogether. I'm only guessing.

           

          Regards,

          Nico

          • 2. Re: How to parse the JSON using JTX?
            user126898 Guru

            To echo Nico here, java was not meant to handle hierarchical json.  Flat JSON basically a CSV it is fine but when you start to get into structs/arrays this is where java breaks down and PwC is by no means able to handle this.

             

            Now if you could use IICS this is done natively within the mapping with no need for JAVA.

            You can see below we can directly source from JSON and the construct the structs and arrays that you have in your source file. 

            You then can flatten this structure into a relational format using the hierarchical processor which is part of Cloud data integration.

             

            Also you can see there is no code to maintain.  Much easier than java. 

            I would highly suggest looking at IICS if you can.

             

            Thanks,

            Scott

             

             

            • 3. Re: How to parse the JSON using JTX?
              Mani Ar Seasoned Veteran

              Thank you Nico! Unfortunately, the below line did not work

               

              JSONArray skus = jsonObject1.getJSONArray( "hits")

               

              We do not have IICS capabilities and since JSON format is mostly used by many clients as the latest format, I keep getting into this problem. I am looking to have a robust Java code that parses the JSON file and converts them into a csv file.

               

              Thanks again,

              Mani A

              • 4. Re: How to parse the JSON using JTX?
                Mani Ar Seasoned Veteran

                Hi,

                 

                Thanks for the response and suggestion. Unfortunately, we are not running on IICS and not planning to get one in near future.

                 

                Thanks,

                Mani A

                • 5. Re: How to parse the JSON using JTX?
                  Nico Heinze Guru

                  As mentioned, I'm by no means a Java expert. If I were you, I would post this problem e.g. on Stack Overflow (www.stackoverflow.com) in the forum; some of the moderators are blatant ... but the technical community is great, and there are real Java experts there. Be careful to mention that you are no Java expert and that you need to get it up and running fast and easily, otherwise you likely get swamped with zillions of responses which won't help you. But as mentioned, the community is great, no question.

                   

                  What exactly does not work about the line I've suggested? Can you post an example of the expected result and the received (wrong) result?

                   

                  Regards,

                  Nico