Consuming a JSON webservice - Part 2 of 2

In part one I showed how to use the web request node to send an HTTP web request to a json webservice. The response was a JSON message. This part shows how to import the JSON message into WEM data fields.

Last year, we introduced the data import and export nodes, a convenient way to import or export excel or CSV files. We recently added the option to import JSON data.

Importing JSON data is different from importing tabular data.

What is a JSON message?

JSON stands for _JavasScript Object Notation_. It describes data in a text-based and human-readable format. JSON is often used in conjunction with REST APIs and web-based services. It is more powerfull than for example CSV, because it isn't restricted to tabular data.

The following example is a JSON message that contains information about a person "John Smith":

    "firstName": "John",
    "lastName": "Smith",
    "age": 35,
    "height": 1.86,
    "isMember": true,
    "email": null,
    "address" : {
        "street": "Lizzy Ansinghstraat 163–3",
        "postcode": "1072 RG",
        "place": "Amsterdam"
    "phoneNumbers" : [
        { "type": "home", "number": "+31 (0)20 240 21 20" },
        { "type": "mobile", "number": "+31 (0)6 123 456 78" }

The basic construct for JSON messages is the so called _object_. An object is a list of "key/value" pairs surrounded by curly brackets ({}). The message itself is an object. In the example above, the line "firstName": "John" is a key/value pair. The key in this case is firstName, and the value is "John".

Key is always surrounded by quotes. The value can be any of the following type:

  • A string (e.g. text). For example, the value "John" is a string. Strings are always surrounded by quotes.
  • A number. In the example above, the age and height are numbers. Numbers are not surrounded by quotes.
  • A boolean (e.g. true or false). In the example, a boolean is used for the key "isMember".
  • The value null can be used when the actual value is unknown or missing.
  • A value can again be an object. This allows for nesting of objects. In the example above, the address is an object that contains the keys "street", "postcode" and "place".
  • A value can also be a list (called an _array_) of values. This list is surrounded by square brackets ([]). In the example above "phoneNumbers" is a list of objects.

An important aspect is that JSON messages can be deeply nested. It isn't unusual to see a message that contains a list of objects, where each object again contains a list of objects, etc...

Importing a JSON message

In part one of this blog we used the Open Movie Database API. This API contains a method to search for movies, given a title and optionally a year. The following query searches for the Jurassic Park movies:

We will now import this JSON response using the data import node. A new drop down is added to the data import node, that allows you to choose the file format. It this case we choose JSON. Next, we have to specify the data source. This sould be the data field that was used by the web request node to store the response body.

If we click Edit Mappings, an overlay appears that allows you to describe the structure of the JSON message, and map the fields and arrays to data fields in WEM. For CSV, you could specify a flat list of columns. JSON has a nested structure however, so you define this structure by building a tree. This tree consists of _objects_, _arrays_, _text fields_, number fields or _"true/false" fields_, which corresponds to the different JSON value types described above. The tree that describes the response of the OMDB query looks as follows:


Now, instead of building this tree structure manually, I used the feature "Create mapping from example". This allows you to copy & paste the JSON result and let WEM infer the JSON structure based on this result. This saves a lot of time, and you can always refine the structure if WEM wasn't able to infer everything from one example.

The next step is to map the the JSON fields to data fields in WEM. For this, I created the following data model in WEM:


For the next step, we have to map the JSON fields to the corresponding WEM data fields. You do this simply by clicking on the field, and choosing the corresponding data field by clicking on the "Map to" button. After all the relevant fields are mapped, the tree should look like this:


Now that we have imported the data, we can simply build a page that shows the results. In my example, I used a repeater and a panel to list all the movies: