JSON

Parsing Json Array

This example shows how to extract and parse a Json array.

Suppose we have the following Json:

{
  "Actors": [
    {
      "name": "Tom Cruise",
      "age": 56,
      "Born At": "Syracuse, NY",
      "Birthdate": "July 3, 1962",
      "photo": "https://jsonformatter.org/img/tom-cruise.jpg"
    },
    {
      "name": "Robert Downey Jr.",
      "age": 53,
      "Born At": "New York City, NY",
      "Birthdate": "April 4, 1965",
      "photo": "https://jsonformatter.org/img/Robert-Downey-Jr.jpg"
    }
  ]
}

.. and we want to extract each Name & Age.

JSon

Age=

Name=

CSVLine=

 

// Set Json variable to some Json value. In practice this would be read from a database or incoming message.

Json={ "Actors": [ { "name": "Tom Cruise", "age": 56, "Born At": "Syracuse, NY", "Birthdate": "July 3, 1962", "photo": "https://jsonformatter.org/img/tom-cruise.jpg" }, { "name": "Robert Downey Jr.", "age": 53, "Born At": "New York City, NY", "Birthdate": "April 4, 1965", "photo": "https://jsonformatter.org/img/Robert-Downey-Jr.jpg" } ] }

 

// Extract the Json path "Actors" and enable the 'Extract All Array Values To CSV' option

ActorsArray=Extract FieldFrom%Json%Json Path"Actors"(All Array Items)

// ActorsArray variable now contains:

// Tom Cruise,56,"Syracuse, NY","July 3, 1962",https://jsonformatter.org/img/tom-cruise.jpg

// Robert Downey Jr.,53,"New York City, NY","April 4, 1965",https://jsonformatter.org/img/Robert-Downey-Jr.jpg

 

// For each line in ActorsArray variable

For EachLine In%ActorsArray%[Assign To: CSVLine]

// Use the Parse CSV action to get specific column values

Parse CSV Line%CSVLine%Set%Name%=Col1,%Age%=Col2

// Name:%Name% , Age: %Age%(Log)

Next Loop

The Extract Field action is used to extract a specific Json Path. If the selected path is an array we can choose to return all items in the array to a comma separated value (CSV):

When extracted the ActorsArray field will contain:

Tom Cruise,56,"Syracuse, NY","July 3, 1962",https://jsonformatter.org/img/tom-cruise.jpg
Robert Downey Jr.,53,"New York City, NY","April 4, 1965",https://jsonformatter.org/img/Robert-Downey-Jr.jpg

We then use the For..Each action to loop over each line in the returned CSV. Each line is assigned to the %CSVLine% variable.

Inside the loop the Parse CSV Line action is then used to get values from column 1 (and assign to the %Name% variable) and column 2 (and assign to the %Age% variable.)

---