Example 03 / REST Import and Transform
A pull in.
Records you can query.
Pull JSON rows from a REST endpoint, stage them, and transform them into application records with a coalescing field map.
Download the manifest ↓Pull
A published Data Source Request action fetches a JSON payload from a REST endpoint and saves the response as an attachment.
Stage
JSONStreamLoader writes each row into an import set staging table with u_-prefixed columns.
Transform
A transform map with a coalescing field map turns staging rows into application records; reruns update instead of duplicating.
Seventeen steps. One repeatable pipeline.
A REST message and connection alias define the source; a published Data Source Request action fetches the payload; an import set data source with row path /users/users stages each row; seven field maps transform them into records. The external-id map coalesces, so importing the same payload twice updates the existing records.
| Step | Operation | Purpose |
|---|---|---|
| Target table | x_snc_duri_user with external_id (mandatory), name, username, email, phone, website and company name. | |
| Staging table | x_snc_duri_user_import extends sys_import_set_row; the JSON loader creates the u_* columns at load time. | |
| REST message | A GET method for the users path with Accept: application/json, shared by the connection alias. | |
| Pull action | Published Data Source Request action bound to the data source; it fetches the payload and stores the response body. | |
| Data source | Type REST, format JSON, row path /users/users, expandNodeChildren for the nested company object. | |
| Transform map | Staging to target with seven field maps; external_id coalesces on u_id with a script fallback. | |
| Scheduled import | On-demand import that the manifest executes once and you can rerun any time. |
Inspect the complete JSON manifest
{
"schemaVersion": 1,
"scope": {
"prefix": "x_snc_duri",
"name": "Demo Users REST Import",
"create": true,
"description": "Import public demo users from the demo source into a scoped table."
},
"updateSet": "Demo Users REST Import",
"steps": [
{
"id": "target_table",
"skill": "table",
"op": "create_table",
"inputs": {
"name": "x_snc_duri_user",
"label": "Demo User",
"sysScope": "$scope",
"schema": [
{
"column": "external_id",
"label": "External ID",
"internalType": "string",
"mandatory": true
},
{
"column": "name",
"label": "Name",
"internalType": "string"
},
{
"column": "username",
"label": "Username",
"internalType": "string"
},
{
"column": "email",
"label": "Email",
"internalType": "string"
},
{
"column": "phone",
"label": "Phone",
"internalType": "string"
},
{
"column": "website",
"label": "Website",
"internalType": "string"
},
{
"column": "company_name",
"label": "Company",
"internalType": "string"
}
]
}
},
{
"id": "staging_table",
"skill": "table",
"op": "create_table",
"inputs": {
"name": "x_snc_duri_user_import",
"label": "Demo User Import",
"superClass": "sys_import_set_row",
"sysScope": "$scope",
"schema": []
}
},
{
"id": "rest_msg",
"skill": "rest-message",
"op": "create",
"inputs": {
"name": "Demo Source REST",
"endpoint": "https://api.example.com",
"description": "Public demo users JSON wrapped as {users:[...]}",
"sysScope": "$scope",
"methods": [
{
"name": "Get Users",
"httpMethod": "GET",
"path": "/demo/users",
"headers": {
"Accept": "application/json"
},
"parameters": {
"wrap": "users"
}
}
]
}
},
{
"id": "alias",
"skill": "connection-alias",
"op": "create",
"inputs": {
"name": "Demo Source REST",
"connectionUrl": "https://api.example.com",
"sysScope": "$scope"
}
},
{
"id": "dsr_action",
"skill": "flow-action",
"op": "create_datasource_action",
"inputs": {
"name": "Import Demo Users REST",
"internalName": "import_demo_users_rest",
"description": "DSR pull of https://api.example.com/demo/users?wrap=users",
"restMessageFunction": "$rest_msg",
"connectionAlias": "$alias",
"resourcePath": "/demo/users",
"httpMethod": "get",
"headers": {
"Accept": "application/json"
},
"queryParams": {
"wrap": "users"
},
"sysScope": "$scope"
}
},
{
"id": "data_source",
"skill": "import-set",
"op": "create_data_source",
"inputs": {
"name": "Demo Users REST Feed",
"importSetTableName": "x_snc_duri_user_import",
"format": "JSON",
"type": "REST",
"jpathRootNode": "/users/users",
"expandNodeChildren": true,
"sysScope": "$scope"
}
},
{
"id": "attach_dsr",
"skill": "flow-action",
"op": "attach_datasource",
"inputs": {
"dataSourceName": "Demo Users REST Feed",
"actionId": "$dsr_action",
"actionType": "datasource_request",
"sysScope": "$scope"
}
},
{
"id": "transform_map",
"skill": "import-set",
"op": "create_transform_map",
"inputs": {
"name": "Demo Users REST to User",
"sourceTable": "x_snc_duri_user_import",
"targetTable": "x_snc_duri_user",
"sysScope": "$scope"
}
},
{
"id": "field_external_id",
"skill": "import-set",
"op": "add_field_map",
"inputs": {
"transformMap": "$transform_map",
"sourceField": "u_id",
"targetField": "external_id",
"coalesce": true,
"useSourceScript": true,
"sourceScript": "answer = (function transformEntry(source) {\n var v = source.u_id;\n if (v) {\n return String(v);\n }\n v = source.id;\n return v ? String(v) : '';\n})(source);",
"sysScope": "$scope"
}
},
{
"id": "field_name",
"skill": "import-set",
"op": "add_field_map",
"inputs": {
"transformMap": "$transform_map",
"sourceField": "u_name",
"targetField": "name",
"sysScope": "$scope"
}
},
{
"id": "field_username",
"skill": "import-set",
"op": "add_field_map",
"inputs": {
"transformMap": "$transform_map",
"sourceField": "u_username",
"targetField": "username",
"sysScope": "$scope"
}
},
{
"id": "field_email",
"skill": "import-set",
"op": "add_field_map",
"inputs": {
"transformMap": "$transform_map",
"sourceField": "u_email",
"targetField": "email",
"sysScope": "$scope"
}
},
{
"id": "field_phone",
"skill": "import-set",
"op": "add_field_map",
"inputs": {
"transformMap": "$transform_map",
"sourceField": "u_phone",
"targetField": "phone",
"sysScope": "$scope"
}
},
{
"id": "field_website",
"skill": "import-set",
"op": "add_field_map",
"inputs": {
"transformMap": "$transform_map",
"sourceField": "u_website",
"targetField": "website",
"sysScope": "$scope"
}
},
{
"id": "field_company",
"skill": "import-set",
"op": "add_field_map",
"inputs": {
"transformMap": "$transform_map",
"sourceField": "u_company_name",
"targetField": "company_name",
"useSourceScript": true,
"sourceScript": "answer = (function transformEntry(source) {\n if (source.u_company_name) {\n return source.u_company_name;\n }\n if (source.company_name) {\n return source.company_name;\n }\n var c = source.u_company || source.company;\n if (c && c.name) {\n return c.name;\n }\n try {\n var parsed = JSON.parse(c);\n if (parsed && parsed.name) {\n return parsed.name;\n }\n } catch (e) {}\n return '';\n})(source);",
"sysScope": "$scope"
}
},
{
"id": "scheduled_import",
"skill": "import-set",
"op": "create_scheduled_import",
"inputs": {
"name": "Demo Users REST On Demand",
"dataSource": "$data_source",
"runType": "on_demand",
"active": true,
"sysScope": "$scope"
}
},
{
"id": "run_import",
"skill": "import-set",
"op": "run_scheduled_import",
"inputs": {
"scheduledImport": "$scheduled_import",
"timeoutSeconds": 180,
"sysScope": "$scope"
}
}
],
"test": {
"structural": true
}
}The published manifest points at a placeholder https://api.example.com source. Before executing, replace the REST message endpoint, connection URL and action resource path with a real JSON endpoint that wraps its array in a named object — ServiceNow needs a row path like /users/users for a named root array.
1. Create and inspect
Download the manifest into a durable project folder and use an authorized development target named dev. It creates the scoped app x_snc_duri (Demo Users REST Import) with its own update set. Keep the manifest and its local results together.
sn preflight manifest.sn.json --target dev --instance-checks
sn plan manifest.sn.json --target dev
sn execute manifest.sn.json --target dev --yes
sn validate manifest.sn.json --target devSet up your instance first if needed.
2. Point it at a real endpoint
The downloaded manifest carries a placeholder source. Edit the REST message endpoint, the connection alias URL and the action's resource path to a JSON endpoint whose payload wraps the array in a named object. The row path /users/users must match that wrapper — a top-level array without a name has no valid row path.
sn patch manifest.sn.json --target dev # adjust the endpoint fields, then redeployJSONStreamLoader stages columns as u_id, u_name, u_company_name and so on, even when the payload keys are unprefixed. Field maps must use the u_ names, and nested objects like company.name only expand when expandNodeChildren is set — this manifest already does both.
3. Run the import. Read back.
Execute the scheduled import, then check the import set, staging rows and transformed records. The external-id field map coalesces, so reruns update the same records instead of creating duplicates.
sn execute manifest.sn.json --target dev --yes # creates artifacts and runs the import once
sn get x_snc_duri_user_import 'sys_idISNOTEMPTY' --target dev --limit 20 --json
sn get x_snc_duri_user 'external_id=1' --target dev --jsonExpected: a processed import set, one staging row per source object, and transformed records in x_snc_duri_user with names, emails and company names carried across.
4. Rerun. Check for drift.
sn execute manifest.sn.json --target dev --yes --resume
sn validate manifest.sn.json --target dev
sn drift manifest.sn.json --target devA rerun pulls the payload again and coalesces on external_id: record count stays stable while values update. Validation and drift checks confirm the deployed configuration is unchanged. For changes to shipped configuration, use sn patch and a new update set.
Observed
Working software. Inspectable results.
A live run on a development instance executed all expanded deployment steps, including the published pull action and the scheduled import.
17
manifest steps executed
All deployment steps succeeded, including the published Data Source Request action and the on-demand import run.
10
rows imported and transformed
The processed import set carried every source object into the target table.
0
duplicates on rerun
The coalescing external-id map updates existing records when the same payload is imported again.
Results are from one development instance and one payload shape. The example is structural (test.structural); it does not ship lifecycle test cases like Catalog Approval. Error handling for unreachable endpoints, malformed payloads and partial transform failures is out of scope — review retry and recovery behavior before adapting it for production.