On this page

Transform JSON with JSONata

Manipulate and transform any JSON document into an easy-to-handle format for your Conversations application. A mapping definition describes how to turn input JSON into output JSON.

Event Type - Mapping Definition Overview

An event type specifies the transformation (mapping) of an external data payload into an API payload suitable for processing by Web1on1, or vice versa. The internal payload for an inbound event should be the Upsert API call for conversations. It contains the contact or conversation identifier(s) and one or more messages to add to the conversation.

Request: POST https://api.web1on1.chat/v2/eventtypes

Authorization: Bearer YOUR-TOKEN
Content-Type: application/json
{
    "name": "tjekvik.checkin.invite",
    "description": "Tjekvik checkin",
    "jsonata": "{ \"id\": $conversationForVIN($organizationForBmwId(BMWDealerId), VIN) }"
}

You must create a mapping definition for each JSON document type you want to transform. Then, you can use mapping definitions with the Mappings API to transform JSON documents from one shape into another.

To do the JSON to JSON transformation, we employ a utility called JSONata, which enables us to do the field mapping from external to internal data and vice-versa.

What is JSONata?

JSONata is a powerful data transformation and manipulation language for JSON data. The runtime library written in JavaScript was originally developed by IBM and later open sourced. JSONata is especially useful when dealing with APIs that return JSON data with different structures, as it can transform the data into a JSON format that is compatible with other APIs.

The JSONata documentation includes many examples for what you do with it. Visit the JSONata Exerciser to verify and/or practice writing JSONata expressions.

Why JSONata in CitNOW Conversations

JSONata assists in manipulating and converting source JSON data into a preferred target format, enabling pain-free deserialization in CN Conversations. The key point is the "preferred target format", since JSONata's capabilities extend beyond addressing issues with source JSON data, enabling the transformation of any JSON data into any desired format. This includes a wide array of additional transformations and manipulations, such as data concatenation, element filtering, various calculations, and much more.

Leveraging JSONata for transformation and manipulation of JSON data can help to reduce ETL programming code inside and outside CN Conversations drastically.

How to use JSONata

Using JSONata is quite simple. You need a source JSON document and a JSONata definition.

A JSONata definition contains all the instructions on how you want to transform and manipulate your source document.

To create and try out a JSONata definition you can use the JSONata Exerciser before using it in your application. JSONata is immensely powerful, and the learning curve can be steep. In this article, I want to give some first, but simple, examples to get up and running with JSONata transformations.

More information on creating transformation and manipulation definitions can be found in the official documentation at www.jsonata.org.

Mapping expression

A mapping expression is a piece of code that specifies how to turn input fields into an output field. The following example shows how you could output a total price from a quantity and a unit price.

{ "total": item.quantity * item.unit_price }

On the left is the name of the output field, on the right is the mapping expression, written in JSONata. You can add more output fields, separated by commas.

{ "total": item.quantity * item.unit_price, "currency": item.currency, "product": product.id }

The result looks just like JSON, even though it technically isn’t, because of the syntax of JSONata.

JSONata Transformations

The Events API takes the incoming message body and applies the configured JSONata transformation on it. It uses a fact that JSONata expression is a superset of JSON document so that by default any valid JSON document is a valid JSONata expression.

For Inbound Events, the JSONata transformation should result in the payload for a conversation Upsert API call. For Outbound Events the JSONata transformation should result in a payload conforming to the external endpoint's supported schema.

Example: Inbound Events

Inbound Events specify transformation from anything to Conversation Upsert payload. As an example, let's take the following incoming message body.

Source JSON

Our source JSON document looks like below. When POSTing the source payload as an event, the transformation output document will Upsert (create or update) a conversation.

Request: POST https://api.web1on1.chat/v2/events/acme.workorder.ready

{
    "dealerId": "kangaroo-647",
    "contact": {
        "FirstName": "John",
        "Surname": "Johnson",
        "Phone": [{ "type": "mobile", "number": "+441172345678" }],
        "Email": [{ "type": "office", "address": "john@example.com" }],
    }
}

Expected JSON

The result of the transformation should look similar to the following CNC JSON document:

{
    "organization": "66c3ed372a33131f1b4ba1b8",
    "type": "contact",
    "contact": {
        "profile": {
            "givenName": "John",
            "familyName": "Johnson",
            "telephone": "+441172345678",
            "email": "john@example.com"
        }
    },
    "messages": [
        {
            "type": "status",
            "text": "Acme Workorder Ready"
        }
    ]
}

JSONata Expression

To quickly iterate on the schema mapping, issue PUT requests to the event type. Make sure the content-type is text/plain, and the resulting JSON matches a typical conversation upsert request.

Request: PUT https://api.web1on1.chat/v2/eventtypes/6663a1f684ba342816c4b7c4/jsonata

{
    "organization": $organizationForExternalId(dealerId),
    "type": "contact",
    "contact": {
        "profile": {
            "givenName": contact.FirstName,
            "familyName": contact.Surname,
            "telephone": contact.Phone[type = "mobile"].number,
            "email": contact.Email[0].address
        }
    },
    "messages": [
        {
            "type": "status",
            "text": "Acme Workorder Ready"
        }
    ]
}

Our definition begins with “{“ which indicates that the result should be a JSON object. Within the object, we define the attributes "organization", "type", "contact" and "messages", required when a conversation ID can not be derived. Then we reference the “fn” attribute from the source document (the assumption is that we always have an “fn” attribute and it has a value). We complete the definition with “}” to close the JSON object.

The example above should give you a quick overview of the possibilities you get with JSONata transformations. Check the JSONata documentation for additional transformation and manipulation capabilities. You can find and tryout many complex expressions in the JSONata playground.

Outbound Events

Native CNC event payloads can be shaped to your specification before they reach your webhook URL. Specify webhook payload transformations to any target format by configuring the service.jsonata property.

Special lookup functions

  • $conversationFor({ organization, email, telephone, MyCarVIN, MyCarLicensePlate })
  • $conversationForVIN(organization, vin)
  • $conversationForLicensePlate(organization, licensePlate)
  • $organizationFor(externalId, BMWDealerId)
  • $organizationForBmwId(id)
  • $organizationForExternalId(id)
  • $locationForExternalId(id)
  • $departmentForExternalId(id)
  • $normalizePhone(organization, phoneNumber)

$conversationFor({ organization, email, telephone, MyCarVIN, MyCarLicensePlate })

Fetch the most suitable conversation ID for an organization and one or more hard (email, telephone) and soft (VIN, license plate) contact identifiers.

This function accepts an object with the following keys:

  • organization (required)

    The organization on behalf of which the message is sent. The organization field can be any Web1on1 organization ID.

  • telephone (optional)

    The telephone number of the contact, if known.

  • email (optional)

    The email address of the contact, if known.

  • MyCarVIN (optional)

    The VIN of the contact's "My Car".

  • MyCarLicensePlate (optional)

    The license plate of the contact's "My Car".

Note: providing both the telephone number and email is recommended, as it has the most chance of finding the relevant contact. In case of conflict (multiple matching contacts), the most likely and most comprehensive conversation is returned. Matches on telephone or email are given preference over VIN and license plate matches.

Example

An event payload such as

{
    "dealerId" : "crazy-kangaroo-96",
    "VIN": "WBY7X410X0SW50618",
    "contact": {
        "email": "john@example.com"
    }
}

could have this jsonata

"id": $conversationFor({
    "organization": $organizationFor("tjekvik:" & dealerId, BMWDealerId),
    "MyCarVIN": VIN,
    "MyCarLicensePlate": licensePlate,
    "email": contact.email,
    "telephone": contact.phone
})

render to JSON with output such as

"id": "66e382b005e502069c8b2daf"

or, if no conversation could be found, as an empty string

"id": ""

$conversationForVIN(organization, vin)

Returns the most recent conversation for a VIN within an organization.

"id": $conversationForVIN($organizationFor("tjekvik:" & dealerId, BMWDealerId), VIN)

could render to JSON with output like

"id": "66e382b005e502069c8b2daf"

$conversationForLicensePlate(organization, licensePlate)

Returns the most recent conversation for a contact's "MyCar" license Plate within an organization.

"id": $conversationForLicensePlate($organizationFor("tjekvik:" & dealerId, BMWDealerId), VIN)

$organizationFor(externalId, BMWDealerId)

Returns the organization ID for the organization having an externalId or BMW Dealer ID configured in its account.

The function takes two optional arguments: externalId and a BMW Dealer Id. It first try to match the BMW DealerID (if provided), and fall back to the externalId lookup if there's no match.

"organization": $organizationFor("tjekvik:" & dealerId, BMWDealerId)

$organizationForExternalId(id)

If the ID of an existing, active Web1on1 organization, we assume that the value provided represents an external organization or location ID: the identifier used in your DMS.

Message Forwarder tries to find the matching Web1on1 dealer organization by searching for the external ID:

  • As a location department's external ID
"organization": $organizationForExternalId(dealerId)

$organizationForBmwId(id)

Returns the ID of the organization that has this BMW Dealer ID configured in its MyBMW service configuration.

"organization": $organizationForBmwId(BMWDealerId)

$locationForExternalId(id)

$departmentForExternalId(id)

$normalizePhone(organization, phoneNumber)