Import from JSON (REST API)

Overview

Import data from an external REST API that returns JSON into Pricefx. The route calls the API, unmarshals the JSON response, and loads the data using a mapper. Supports array responses, nested objects, and JOLT transformations.

Files

routes/import-products-from-api.xml

XML

<routes xmlns="http://camel.apache.org/schema/spring">
    <route id="import-products-from-api">
        <from uri="timer://apiImport?repeatCount=1"/>
        <log message="Fetching products from external API" loggingLevel="INFO"/>

        <!-- Call external REST API -->
        <setHeader name="Authorization">
            <simple>Bearer {{api.token}}</simple>
        </setHeader>
        <to uri="pfx-rest:get?url={{api.base.url}}/products&amp;connection=externalApi"/>
        
        <!-- Unmarshal JSON array response -->
        <to uri="pfx-json:unmarshal"/>

        <!-- Load into Pricefx -->
        <to uri="pfx-api:loaddata?objectType=P&amp;mapper=import-products-from-api.mapper&amp;businessKeys=sku"/>

        <log message="API import complete" loggingLevel="INFO"/>
    </route>
</routes>

mappers/import-products-from-api.mapper.xml

XML

<mappers>
    <loadMapper id="import-products-from-api.mapper">
        <body in="productCode" out="sku"/>
        <body in="productName" out="label"/>
        <body in="unitOfMeasure" out="unitOfMeasure"/>
        <body in="currency" out="currency"/>
        <body in="category" out="attribute1"/>
        <body in="listPrice" out="attribute2" converterExpression="stringToDecimal"/>
        <groovy expression="body.status == 'ACTIVE' ? '1' : '0'" out="attribute3"/>
    </loadMapper>
</mappers>

config/application.properties (additions)

# External API connection
integration.connections.externalApi.type=rest
integration.connections.externalApi.url={{api.base.url}}
integration.connections.externalApi.auth.type=oauth2
integration.connections.externalApi.auth.tokenUrl={{api.token.url}}
integration.connections.externalApi.auth.clientId={{api.client.id}}
integration.connections.externalApi.auth.clientSecret={{api.client.secret}}

# API settings
api.base.url=https://erp.example.com/api/v2
api.token.url=https://erp.example.com/oauth/token
api.client.id=${API_CLIENT_ID}
api.client.secret=${API_CLIENT_SECRET}
api.token=${API_BEARER_TOKEN}

With JOLT Transformation (Complex JSON)

When the API response has a nested structure that doesn't map directly to flat Pricefx fields, use JOLT transformation:

API Response (nested)

JSON

{
  "data": {
    "products": [
      {
        "id": "SKU-001",
        "details": {
          "name": "Widget A",
          "pricing": { "list": 99.99, "currency": "USD" }
        },
        "metadata": { "category": "Electronics", "active": true }
      }
    ]
  }
}

routes/import-nested-json.xml

XML

<routes xmlns="http://camel.apache.org/schema/spring">
    <route id="import-nested-json">
        <from uri="timer://nestedApiImport?repeatCount=1"/>

        <to uri="pfx-rest:get?url={{api.base.url}}/products&amp;connection=externalApi"/>
        <to uri="pfx-json:unmarshal"/>

        <!-- Extract the products array from nested response -->
        <setBody>
            <groovy>body.data.products</groovy>
        </setBody>

        <to uri="pfx-api:loaddata?objectType=P&amp;mapper=import-nested-json.mapper&amp;businessKeys=sku"/>
    </route>
</routes>

mappers/import-nested-json.mapper.xml

XML

<mappers>
    <loadMapper id="import-nested-json.mapper">
        <body in="id" out="sku"/>
        <groovy expression="body.details?.name" out="label"/>
        <groovy expression="body.details?.pricing?.list?.toString()" out="attribute1" converterExpression="stringToDecimal"/>
        <groovy expression="body.details?.pricing?.currency" out="currency"/>
        <groovy expression="body.metadata?.category" out="attribute2"/>
        <groovy expression="body.metadata?.active ? '1' : '0'" out="attribute3"/>
    </loadMapper>
</mappers>

How It Works

Timer Trigger: Fires once on startup. For scheduled runs, use scheduler or quartz.
REST Call: pfx-rest:get calls the external API. The connection=externalApi references the connection configured in properties (handles OAuth2 token refresh automatically).
JSON Unmarshal: pfx-json:unmarshal converts the JSON response to a List<Map> (for arrays) or Map (for objects).
Nested Extraction: For nested responses, use <groovy>body.data.products</groovy> to extract the array before loading.
Mapper: Maps JSON field names to Pricefx fields. Use <groovy> expressions to navigate nested objects with null-safe operators (?.).

Common Pitfalls

JSON arrays vs objects: pfx-api:loaddata expects a List<Map>. If the API returns a single object, wrap it: <groovy>[body]</groovy>.
Nested field access: Use <groovy> expressions with ?. (null-safe) for nested fields. Direct <body> mapping only works for top-level flat fields.
OAuth2 token expiry: Configure the connection with auth.type=oauth2 and IM handles token refresh automatically. Do not hardcode tokens.
Large API responses: If the API returns 50k+ records, consider paginated fetching with a loop pattern or request the API to support pagination.
API rate limiting: External APIs may rate-limit requests. Add delay to the timer or use throttle in the route if calling APIs in a loop.
Credentials: Never hardcode API credentials. Use ${ENV_VAR} for secrets or {{property}} for IM configuration.