Help Desk

Submit a ticket My Tickets
Welcome
Login

Advanced JSON Transformation

The following JSON illustrates an inventory of items in a store. In this store, there are multiple book titles with details, but limited details about the other inventory.

In this example, we will create a product inventory list with the following columns:

  • PROD_TYPE
  • DESCRIPTION
  • PRICE

Three JSON commands will be required to parse this content and collect the required data. Once the data is parsed from the JSON data, it will then be combined into a single tabular output via an Advanced Query.

{
  "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
    }],
    "ball": [{
      "color": "blue",
      "price": 9.95
    }]
  }
}

Step 1: Fetch JSON Data

  1. Create a new Chain and name it “Advanced JSON Transformation”. 
  2. Add the first Command and select HTTP -> GET operation.

    (Hint: use the Find tool to search for "HTTP" and then search for "GET".)

  3. Edit the "GET" Command with the following settings:

    ValueSetting
    Command NameGet Store Inventory
    URLSet to the Workspace variable "store-inventory-json"
  4. Save the Command. 

Step 2: Parse JSON Data

Book Details

Once the data is available, now it is time to parse the book details:

  1. Add an Array to CSV in the JSON BizApp Command with the following parameters:

    ParameterValue
    NameParse Book Data
    Input FileThe output from Get Store Inventory
    Path to Root"store" <press enter>
    "book" <press enter>
    Preview ResultTrue

    ColumnValueJsonPath
    Column 1CATEGORY.category
    Column 2AUTHOR.author
    Column 3TITLE.title
    Column 4PRICE.price
  2. Save the Command.

Bicycle Details

Using the same JSON payload, we will now parse the Bicycle details:

  1. Add an Array to CSV in the JSON BizApp command with the following parameters:

    ParameterValue
    NameParse Ball Data
    Input FileThe output from Get Store Inventory
    Path to Root"store" <press enter>
    "bicycle" <press enter>
    Preview ResultTrue

    ColumnValueJsonPath
    Column 1COLOR.color
    Column 4PRICE.price
  2. Save the Command.

Ball Details

Once the data is available, now it is time to parse the Ball details:

  1. Add an Array to CSV in the JSON BizApp command with the following parameters:

    ParameterValue
    NameParse Ball Data
    Input FileThe output from Get Store Inventory
    Path to Root"store" <press enter>
    "ball" <press enter>
    Preview ResultTrue

    ColumnValueJsonPath
    Column 1COLOR.color
    Column 4PRICE.price
  2. Save the Command.

Chain Status

Your Chain should look like the following:

Step 3: Combine the Data into a Single Data Stream

Combine the data into a single data stream Now that the JSON data has been parsed, it is time to combine the data into a single data output that can be used to load to a variety of targets.

  1. Select the Tabular Transformation -> Advanced Query Command.

    (Hint: use the Find tool to search for "Tabular Transformation" and then search for "Advanced Query")

  2. Connect the "Parse Book Data", "Parse Bike Data", and "Parse Ball Data" Commands to the "Advanced Query" Command.

  3. Add 3 tables with the following values:

    FileTable Name
    Output from Parse Book Databooks
    Output from Parse Bicycle Databicycles
    Output from Parse Ball Databalls
  4. Set the query to the following:  
    (Suggestion: Use copy & paste):

    SELECT "Book" AS PROD_TYPE, TITLE || ' (' || AUTHOR || ') - ' || CATEGORY AS DESCRIPTION, PRICE FROM books
    UNION
    SELECT "Bicycle" AS PROD_TYPE, COLOR AS DESCRIPTION, PRICE FROM bicycles
    UNION
    SELECT "Ball" AS PROD_TYPE, COLOR AS DESCRIPTION, PRICE FROM balls
  5. Set the remaining settings:

    ParameterValue
    Input DelimiterTab
    Output DelimiterTab
    Preview ResultsTrue

Step 4: Publish and Run the Chain

Before the Chain can be run, it needs to be published. After the Chain is published, it can be run.

  1. Publish the Chain, by clicking the PUBLISH button on the upper bar.
  2. Navigate to the Execute area of OneCloud by clicking on the EXECUTE button on the upper bar.
  3. Run the Chain.
  4. The results of the final Command should be the following data:

Step 5: Validate

Confirm that your data looks like the following: 

PROD_TYPEDESCRIPTIONPRICE 
Ballblue9.95
Bicyclered19.95
BookMoby Dick (Herman Melville) - fiction8.99
BookSayings of the Century (Nigel Rees) - reference8.95
BookSword of Honour (Evelyn Waugh) - fiction12.99
BookThe Lord of the Rings (J. R. R. Tolkien) - fiction22.99

What's Next?

> Return to Start

B
Brian is the author of this solution article.

Did you find it helpful? Yes No

Send feedback
Sorry we couldn't be helpful. Help us improve this article with your feedback.