Skip to content

Using placeholders

Suppose we need to parse the following more complex JSON string. In addition, some elements are nested.

Input JSON

[
    {
        "FirstName":"Alice",
        "LastName":"Smith",
        "Address": {
            "City": "New York"
        },
        "Phones": [
            { 
                "Type": "Mobile",
                "Number": "+420721234567" 
            },
            { 
                "Type": "Home",
                "Number": "+420722345678" 
            }
        ]
    },
    {
        "FirstName":"Bob",
        "LastName":"Johnson",
        "Address": {
            "City": "London"
        },
        "Phones": [
            { 
                "Type": "Mobile",
                "Number": "+420723456789" 
            }
        ]
    }
]

We need to fill in the following schema, which does not match the input JSON:

Output schema

Column	Data type	Description
Source	`string`	Name of a data source - example of constant.
Surname	`string`	Surname of user - example local token.
Location	`string`	Location of user - example nested token.
Phone	`string`	First available phone - example of using JSONPath.
JSON	`string`	Currently processed JSON token.

To achieve the desired result, we need to complete the mapping as follows:

Mapping

Key (column)	Value (mapping)	Description
Source	`${fixed:Import}`	Constant `Import`.
Surname	`string`	Using local property `Surname`.
Location	`Address.City`	Using property `City` in the nested object `Address`.
Phone	`$.Phones[0].Number`	Using property `Number` of the the first object in the nested `Phones` array.
JSON	`${body.row}`	A JSON string containing the entire token currently being processed (not the entire input being processed).

Result

Source	Surname	Location	Phone	JSON
Import	Smith	New York	+420721234567	`{ "FirstName": "Alice", "LastName": "Smith", "Address": { "City": "New York" }, "Phones": [ { "Type": "Mobile", "Number": "+420721234567" }, { "Type": "Home", "Number": "+420722345678" } ] }`
Import	Johnson	London	+420723456789	`{ "FirstName": "Bob", "LastName": "Johnson", "Address": { "City": "London" }, "Phones": [ { "Type": "Mobile", "Number": "+420723456789" } ] }`