Consuming JavaScript Object Notation File in AI Spoon

Version 4
    Share:|

    Hi All,

     

    I am posting this because it took me a while to even start reading the Java Script Object Notation File. I was not able to find much concentrated document pieces on this topic apart from 3 very basic sample job which ships with Spoon.

     

    I have to work on a CMDB feed where i need to Consume JSON file and pump it into Computer System Class. I am using here a sample file.

     

    (1)

    very first thing which i came to know was - File which i was consuming was not even the Correct JSON formatted File. So i found below website to check and verify Content of a JSON file. File should Not be too big.

     

    http://jsonformatter.curiousconcept.com/

     

    Attached is the Sample Correct JSON File. Copy the Content and paste to Above Web Site. It will show you valid/Invalid.

     

    Capture.PNG

     

    Correct format of JSON file important. If Format is not correct Spoon Gives some unexpected error which never says that spoon is complaining about incorrect JSON File format.

     

    (2) Now Reading the JSON File in Spoon. You need to Define the File Path After Validating the JSON File in step 1.

     

    Capture.PNG

     

    (3) Now Mapping the JSON File data with Columns in Spoon Job. Small Tricky Part is Traversing to the data. Its Like Traversal of multidimensional Array.

     

    Sample Data-

     

    { "store": {

        "book": [

          { "category": "reference",

            "author": "Nigel Rees",

            "title": "Sayings of the Century",

            "price": 8.95

          },

          { "category": "fiction",

            "author": "Evelyn Waugh",

            "title": "Sword of Honour",

            "price": 12.99

          },

          { "category": "fiction",

            "author": "Herman Melville",

            "title": "Moby Dick",

            "isbn": "0-553-21311-3",

            "price": 8.99

          },

          { "category": "fiction",

            "author": "J. R. R. Tolkien",

            "title": "The Lord of the Rings",

            "isbn": "0-395-19395-8",

            "price": 22.99

          }

        ],

        "bicycle": {

          "color": "red",

          "price": 19.95

        }

      }

    }

     

    Traversal Should be Done From Top Node.

     

    Like

    $.store.book[*].category To Fetch/Read All book's Category data.

    $.store.book[1].category  Fetch Book 1 Category data


    Capture.PNG

     

     

    View Mapped Data which is Now ready for Consumption .

     

    Capture.PNG

     

     

    Hope this will help as it took me a long time first time.

     

    Note- Data here is used is just for example Purpose. This is tested on Kettle 4.1. One Sample JSON/JS file is attached here for your reference.

     

    Regards

    Rahul Priyadarshy