--- title: "JSON to SurveySparrow Ticket Migration: Technical Guide" slug: json-to-surveysparrow-ticket-migration-technical-guide date: 2026-08-05 author: Rishabh categories: [Migration Guide, Help Desk] excerpt: "Technical guide to migrating JSON ticket data into SurveySparrow Ticket Management via the v3 API, covering field mapping, comments, attachments, and edge cases." tldr: "No native JSON import exists in SurveySparrow. Migrate via the v3 API, map priority/status to numeric IDs, handle comments separately, and store original timestamps in custom fields since created_at is not settable." canonical: https://clonepartner.com/blog/json-to-surveysparrow-ticket-migration-technical-guide/ --- # JSON to SurveySparrow Ticket Migration: Technical Guide # JSON to SurveySparrow Ticket Migration: Technical Guide *Last verified against SurveySparrow API v3. API documentation reference: [SurveySparrow v3 API](https://developers.surveysparrow.com/rest-apis)* Migrating ticket data from JSON files into SurveySparrow Ticket Management means transforming arbitrary, schema-less records into a strict ticketing system with specific field requirements and numeric integer identifiers. There is no native JSON import tool in SurveySparrow. Every migration requires parsing your JSON source, mapping fields to SurveySparrow's ticket schema, pre-creating contacts and custom fields, and loading data through the SurveySparrow REST API (v3). Whether your JSON export originates from a legacy in-house tool, a NoSQL database dump, or a custom script from another help desk, you are moving from an infinitely flexible data format into a relational ticketing architecture that enforces strict creation order and data types. A simple script will fail at scale. You need a resilient pipeline that handles memory constraints, manages API rate limits, guarantees idempotency to prevent duplicate records, and handles pagination on every list retrieval call. This guide covers the complete technical path: SurveySparrow's ticket data model, JSON schema analysis, stream parsing for large files, dynamic priority/status ID resolution, field-by-field mapping, loading strategies (single vs. batch), contact resolution with pagination, attachment constraints, rate limiting, async and concurrent migration patterns, partial batch failure remediation, and the edge cases that cause silent data loss. > [!WARNING] > **No native JSON importer exists.** SurveySparrow does not offer a built-in tool to upload JSON files as tickets. The platform supports CSV import for contacts only — not for tickets. Ticket creation must go through the API. Plan for a custom ETL pipeline from day one. For other migrations into SurveySparrow, see our [Zammad to SurveySparrow Ticket Migration Guide](https://clonepartner.com/blog/blog/zammad-to-surveysparrow-ticket-migration-technical-guide/). ## SurveySparrow Ticket Data Model Before writing any transformation code, understand the target schema. SurveySparrow Ticket Management is built around discrete tickets generated from survey responses, NPS detractors, or form submissions — not from free-form inbound messages. Data is separated into distinct objects that must be created in a specific order: contacts first, then tickets, then comments. Each ticket has these core fields: | Field | Type | Required | Constraints | |---|---|---|---| | `subject` | string | **Yes** | Max 200 characters | | `priority` | number | **Yes** | Integer ID — account-specific; resolve via Ticket Fields API | | `status` | number | **Yes** | Integer ID — account-specific; resolve via Ticket Fields API | | `description` | string | No | Supports `@email` mentions | | `email` | string | No | Requester's contact email | | `requester_id` | number | No | Existing contact ID in SurveySparrow | | `assignee_id` | number | No | User ID of the assigned agent | | `team_id` | number | No | Team ID | | `template_id` | number | No | Ticket template ID | | `source` | number | No | Integer ID for ticket source channel; resolve valid values via Ticket Fields API | | `custom_fields` | object | No | Key-value pairs matching pre-configured field internal names (case-sensitive) | | `parent_ticket_id` | number | No | For parent-child ticket relationships | | `child_ticket_ids` | number [] | No | Array of child ticket IDs | | `attachments` | binary | No | pdf, png, jpeg, mp3, csv, wav only; max 15 MB per file | The three required fields — `subject`, `priority`, `status` — are non-negotiable. If your JSON records lack any of these, you must derive or assign default values during transformation. > [!NOTE] > **Priority and status are integer identifiers, not strings.** You cannot pass `"priority": "High"` — you must pass the corresponding integer value. These mappings are account-specific and cannot be assumed from examples in documentation. Use the Ticket Fields API (`GET /v3/ticket-fields`) to retrieve the exact ID-to-label mappings for your SurveySparrow instance before writing any transformation logic. The code section below shows how to parse this response programmatically. ## Resolving Priority and Status IDs Dynamically This step eliminates the most common source of silent failures in SurveySparrow migrations. Priority and status values are stored as integer identifiers whose values vary per account. Do not hardcode assumed mappings. Instead, retrieve them from the Ticket Fields API and build lookup dictionaries before your migration begins. ```python import requests def build_field_maps(base_url, headers): """ Retrieves ticket field definitions and returns lookup dicts for priority and status: label (lowercase) → integer ID. """ response = requests.get(f"{base_url}/v3/ticket-fields", headers=headers) response.raise_for_status() fields = response.json().get("data", []) priority_map = {} status_map = {} source_map = {} for field in fields: field_name = field.get("name", "").lower() options = field.get("options", []) if field_name == "priority": for opt in options: priority_map[opt["label"].lower()] = opt["id"] elif field_name == "status": for opt in options: status_map[opt["label"].lower()] = opt["id"] elif field_name == "source": for opt in options: source_map[opt["label"].lower()] = opt["id"] return priority_map, status_map, source_map # Example usage: # priority_map = {"low": 1001, "medium": 1002, "high": 1003, "urgent": 1004} # status_map = {"open": 2001, "pending": 2002, "resolved": 2003, "closed": 2004} # (Actual IDs depend on your account configuration) ``` This pattern also works for the `source` field, which appears in the schema table but has no universally documented values — its valid integer IDs are likewise account-specific and must be resolved from the same endpoint. Store the resulting maps in memory at migration startup and pass them into every transformation function. Never call this endpoint per-ticket — one call at initialization is sufficient. ## Analyzing Your JSON Source Schema JSON ticket exports come in wildly different shapes depending on their origin — a legacy helpdesk export, a custom CRM dump, a webhook archive, or a hand-built dataset. Before you can map anything, you need to audit what you actually have. **Step 1: Profile the JSON structure.** Is it a flat array of objects? Nested with conversations inside tickets? A single NDJSON file or a directory of per-ticket files? The SurveySparrow API expects one flat ticket object per creation call, so any nesting must be flattened. **Step 2: Identify required field equivalents.** Find the fields in your JSON that correspond to `subject`, `priority`, and `status`. Common patterns: - `title`, `name`, `summary` → `subject` - `priority`, `severity`, `urgency` → `priority` (must convert string labels to your account's integer IDs via the map built above) - `status`, `state`, `stage` → `status` (same integer conversion required) **Step 3: Catalog every field you want to preserve.** Any field that doesn't map to a SurveySparrow default field must go into `custom_fields`. You need to create these in SurveySparrow *before* migration and note their exact `internal_name` values — these keys are case-sensitive and mismatches are silently dropped. **Step 4: Check for nested data.** If your JSON has threaded replies, conversation history, or internal notes nested under each ticket, those must be migrated separately using the Ticket Comments API (`POST /v3/ticket-comments`). The ticket creation endpoint does not accept comments inline. ```json // Example: Common JSON ticket structure { "id": "TK-4821", "title": "Login page returns 500 after password reset", "body": "User reports consistent 500 error on /auth/login...", "priority": "high", "status": "open", "reporter_email": "[email protected]", "assigned_to": "[email protected]", "tags": ["auth", "critical"], "created": "2025-11-03T14:22:00Z", "comments": [ {"author": "[email protected]", "text": "Reproduced on staging.", "at": "2025-11-03T15:01:00Z"} ] } ``` This single record requires at least four separate API operations in SurveySparrow: resolve/create the contact, create the ticket, add the comment, and optionally map `tags` to a custom multi-select field. ## Parsing Large JSON Files Loading a 5 GB JSON export into memory using `JSON.parse()` in Node.js or `json.load()` in Python will cause out-of-memory (OOM) fatal errors. Standard parsing libraries read the entire file into RAM before execution. For enterprise migrations, use stream parsing. This lets your script read the JSON file sequentially, process one ticket object at a time, and release the memory. ### Python: ijson `ijson` is the standard library for iterative JSON parsing in Python. It yields objects as they are parsed from the file stream. ```python import ijson def process_json_export(file_path): with open(file_path, 'rb') as f: # Assuming the JSON is an array of ticket objects tickets = ijson.items(f, 'item') for ticket in tickets: process_ticket(ticket) ``` ### Node.js: JSONStream In Node.js, `JSONStream` piped from the native `fs` module achieves the same result without blocking the event loop. ```javascript const fs = require('fs'); const JSONStream = require('JSONStream'); fs.createReadStream('export.json') .pipe(JSONStream.parse('*')) .on('data', function(ticket) { processTicket(ticket); }); ``` One bad JSON record in a streaming parse can halt your entire pipeline. Wrap each record's processing in a try/catch and validate that required fields exist, types are correct, and strings are within length limits before submitting to the API. ## Field Mapping: JSON to SurveySparrow Tickets Here's a practical mapping table for the most common JSON fields: | JSON Source Field | SurveySparrow Target | Transformation Notes | |---|---|---| | `title` / `subject` / `summary` | `subject` | Truncate to 200 chars. Log truncations. | | `body` / `description` / `content` | `description` | HTML is stored but rendered as plain text in some views. | | `priority` (string) | `priority` (integer) | Resolve via `build_field_maps()` — do not hardcode numeric values | | `status` (string) | `status` (integer) | Resolve via `build_field_maps()` — do not hardcode numeric values | | `source` (string) | `source` (integer) | Resolve via `build_field_maps()` — valid values are account-specific | | `reporter_email` / `customer_email` | `email` | SurveySparrow auto-creates a contact if none exists | | `assignee` / `agent_email` | `assignee_id` | Must resolve email → SurveySparrow user ID via Users API | | `team` / `group` | `team_id` | Must resolve name → team ID via Teams API | | `tags` / `labels` | `custom_fields.tags` | Create a multi-select custom field first | | `created_at` / `created` | *(not settable)* | SurveySparrow sets `created_at` to API call time. Store originals in a custom field. | | `comments` / `replies` | Ticket Comments API | Separate `POST` per comment after ticket creation | | `attachments` | `attachments` (multipart) | Only pdf, png, jpeg, mp3, csv, wav. Max 15 MB. | > [!CAUTION] > **You cannot set `created_at` or `updated_at` on tickets via the API.** SurveySparrow assigns these timestamps at creation time. If preserving original ticket timestamps is a hard requirement, store the original value in a custom Date field (e.g., `original_created_at`). This is a common deal-breaker for compliance-driven migrations. ### Handling Timestamps JSON exports often feature timestamps in Unix epoch format (e.g., `1691234567`) or custom string formats. SurveySparrow's API expects ISO 8601 formatted strings for custom Date fields (e.g., `2023-08-05T12:34:56Z`). Be aware of timezone offsets. If your JSON source exported dates in local time without an offset, convert them to UTC before storing in custom Date fields to prevent chronological sorting errors in the SurveySparrow UI. ## Authentication and API Base URL SurveySparrow uses **OAuth 2.0** for API authentication. You can also generate a personal access token from Settings → Apps & Integrations → Create a Custom App. The API base URL depends on your account's data center region. SurveySparrow offers hosting in India (Mumbai), USA (Virginia), Canada (Central), and EU (Frankfurt). You must use the correct regional base URL — contact `[email protected]` if you're unsure which data center your account is on. ```bash # Standard US endpoint curl --request POST \ --url 'https://api.surveysparrow.com/v3/tickets' \ --header 'Authorization: Bearer YOUR_ACCESS_TOKEN' \ --header 'Content-Type: multipart/form-data' \ --form 'subject=Login page returns 500 after password reset' \ --form 'description=User reports consistent 500 error...' \ --form 'priority=1003' \ --form 'status=2001' \ --form '[email protected]' ``` Note that the `priority` and `status` values above (1003, 2001) are illustrative placeholders. Replace them with the actual integer IDs retrieved from your account's Ticket Fields API. > [!TIP] > **The access token is displayed only once when generated.** If you lose it, you must regenerate. Store it securely in your ETL configuration from the start. ## Pre-Migration Setup in SurveySparrow Before loading any tickets, configure SurveySparrow to accept your data. ### Create Custom Ticket Fields Any JSON field that doesn't map to a default SurveySparrow field needs a custom field. Navigate to **Settings → Ticket Management → Ticket Fields → Add Field**. Supported custom field types: **Text** (single-line or multiline), **Dropdown**, **Multi-select**, **Date**, and **Nested** (hierarchical dropdown). Create every field before starting the migration — the batch API will silently drop `custom_fields` keys that don't match an existing field's `internal_name`. **Custom field `internal_name` values are case-sensitive.** A field created as `Original_Created_Date` must be referenced exactly that way in every API payload. There is no validation error on mismatch — the key is silently ignored and the data is lost. Retrieve internal names and IDs programmatically before migration: ```bash curl --request GET \ --url 'https://api.surveysparrow.com/v3/ticket-fields' \ --header 'Authorization: Bearer YOUR_ACCESS_TOKEN' ``` ### Pre-Create Contacts with Pagination Handling SurveySparrow requires a valid contact when creating a ticket. Passing an `email` in the ticket creation request causes SurveySparrow to auto-create a contact if one doesn't exist. This is convenient but means you lose control over contact metadata (name, phone, company). For a cleaner migration: 1. Extract unique requester emails from your JSON 2. Create contacts via `POST /v3/contacts` with full metadata 3. Use the returned `id` as `requester_id` in ticket creation 4. Cache the mapping of email → `contact_id` in a local SQLite database to avoid redundant API calls When verifying existing contacts via `GET /v3/contacts`, the endpoint returns paginated results. Failure to handle pagination means your lookup cache will be incomplete, causing duplicate contact creation on large accounts. ```python def get_all_contacts(base_url, headers): """Fetches all contacts handling pagination.""" contacts = [] page = 1 per_page = 100 # adjust based on API constraints while True: response = requests.get( f"{base_url}/v3/contacts", headers=headers, params={"page": page, "per_page": per_page} ) response.raise_for_status() data = response.json() batch = data.get("data", []) contacts.extend(batch) # Stop when fewer results than page size are returned if len(batch) < per_page: break page += 1 return {c["email"]: c["id"] for c in contacts if c.get("email")} ``` Apply the same pagination pattern when building lookup maps for `GET /v3/users` (agents) and `GET /v3/teams`. Any of these calls on a large account will return incomplete results without pagination handling. ## Two Loading Strategies: Single vs. Batch API SurveySparrow offers two ticket creation endpoints. Choose based on your volume and requirements. ### Single Ticket API — `POST /v3/tickets` - **Content type:** `multipart/form-data` (required if uploading attachments) - **Response:** `200` with the created ticket object including its ID - **Use when:** You need the ticket ID immediately (e.g., to add comments), or you're uploading attachments per ticket This is the synchronous path. You call it, get a ticket ID back, then use that ID to add comments via the Ticket Comments API. This is the only reliable way to migrate tickets with threaded replies. **Sample ticket creation response:** ```json { "data": { "id": 12345, "subject": "Login page returns 500 after password reset", "priority": 1003, "status": 2001, "created_at": "2024-03-15T09:22:11Z", "requester_id": 67890 } } ``` The field path `resp.json() ["data"]["id"]` is how you extract the created ticket ID for subsequent comment and attachment calls. ### Batch Ticket API — `POST /v3/tickets/batch` - **Content type:** `application/json` - **Accepts:** An array of ticket objects - **Response:** `202 Accepted` with a `token` for status polling - **Status check:** `GET /v3/tickets/batch/status/{token}` - **Use when:** Migrating large volumes of simple tickets without attachments or comments ```json [ { "subject": "Issue with billing", "priority": 1002, "status": 2001, "email": "[email protected]", "description": "Customer reports duplicate charge..." }, { "subject": "Feature request: dark mode", "priority": 1001, "status": 2001, "email": "[email protected]" } ] ``` > [!WARNING] > **The batch endpoint is asynchronous.** It returns a token, not ticket IDs. You must poll the status endpoint to confirm completion. You **cannot** use the batch API if you need to immediately add comments or attachments — you won't have the ticket IDs at call time. ### Handling Partial Batch Failures The batch endpoint returns `202 Accepted` immediately regardless of whether individual ticket records within the batch will succeed or fail. The status token response, when polled to completion, contains a breakdown of successes and failures. Always poll to completion and process failures explicitly: ```python import time import requests def poll_batch_status(base_url, headers, token, poll_interval=5, max_polls=120): """ Polls batch status until complete or max_polls reached. Returns (succeeded_ids, failed_records). """ for _ in range(max_polls): resp = requests.get( f"{base_url}/v3/tickets/batch/status/{token}", headers=headers ) resp.raise_for_status() result = resp.json().get("data", {}) state = result.get("status", "") if state == "completed": succeeded = [r["id"] for r in result.get("succeeded", [])] failed = result.get("failed", []) return succeeded, failed if state == "failed": raise Exception(f"Batch job failed entirely: {result}") time.sleep(poll_interval) raise TimeoutError(f"Batch status polling timed out for token {token}") def requeue_failed_records(failed_records, state, base_url, headers, priority_map, status_map): """ Re-submits records that failed in a batch via the single ticket API, which provides per-record error details. """ for record in failed_records: source_id = record.get("source_id") error = record.get("error", "unknown") print(f"Retrying failed record {source_id} (batch error: {error})") # Fall back to single API for granular error visibility create_ticket_single(record, state, base_url, headers, priority_map, status_map) ``` When a batch record fails, re-submit it through the single ticket API. The synchronous endpoint returns specific error messages per field, which the batch status response does not always provide. ### Decision Matrix | Factor | Single API | Batch API | |---|---|---| | Attachments | ✅ Supported (multipart) | ❌ Not supported | | Comments after creation | ✅ Immediate (synchronous ID) | ❌ Requires polling first | | Throughput | Lower (1 req per ticket) | Higher (many per request) | | Error granularity | Per-ticket errors immediately | Aggregate status via token; failures need re-queuing | | Custom fields | ✅ | ✅ | | Parent-child linking | ✅ (`parent_ticket_id`) | ❌ Not in batch schema | | Partial failure remediation | N/A | Re-submit failures via single API | ## Concurrent and Async Migration Patterns For migrations exceeding 10,000 tickets, a strictly sequential single-ticket loop is the bottleneck. Parallelizing with a thread pool or async event loop can increase throughput significantly while respecting rate limits via a semaphore. ### Python: ThreadPoolExecutor with Rate Limiting ```python from concurrent.futures import ThreadPoolExecutor, as_completed import threading import time import requests # Semaphore limits concurrent in-flight requests rate_semaphore = threading.Semaphore(5) # max 5 concurrent requests def create_ticket_with_semaphore(ticket_data, state, base_url, headers, priority_map, status_map): with rate_semaphore: result = create_ticket_single(ticket_data, state, base_url, headers, priority_map, status_map) time.sleep(0.2) # ~5 req/sec sustained return result def migrate_tickets_parallel(tickets, state, base_url, headers, priority_map, status_map, max_workers=5): with ThreadPoolExecutor(max_workers=max_workers) as executor: futures = { executor.submit( create_ticket_with_semaphore, t, state, base_url, headers, priority_map, status_map ): t["id"] for t in tickets } for future in as_completed(futures): source_id = futures[future] try: ss_id = future.result() if ss_id: print(f"Migrated {source_id} → {ss_id}") except Exception as e: print(f"Error migrating {source_id}: {e}") ``` **Recommended concurrency levels by migration phase:** | Phase | Recommended Concurrency | Rationale | |---|---|---| | Contact pre-creation | 3–5 workers | Lower risk; contacts are simpler objects | | Ticket creation (no attachments) | 5–10 workers | Test from 3 up; watch for 429 responses | | Comment insertion | 3–5 workers | Sequential per-ticket order must be preserved | | Attachment upload | 1–2 workers | File I/O and multipart encoding are CPU-bound | Start at the lower bound and increase only if you observe zero 429 responses for 10+ minutes. Log every response status code to detect silent throttling. ### Phase-Based Migration for Large Volumes For migrations exceeding 100,000 tickets with attachments and comments, a three-phase approach avoids the single-ticket API's throughput limitations: 1. **Phase 1:** Batch-create all tickets (no attachments/comments). Record all returned ticket IDs via batch status polling. 2. **Phase 2:** Insert comments for each ticket using stored ticket IDs. Use concurrent workers with per-ticket sequential ordering. 3. **Phase 3:** Upload attachments per ticket using multipart single API. Serialize attachment uploads per ticket to avoid partial file state. This approach reduces total API call duration by 60–80% compared to fully sequential single-ticket loading, because Phase 1 processes tickets in batches of hundreds rather than one at a time. ## Migrating Ticket Comments Most real-world JSON exports include threaded replies or notes per ticket. SurveySparrow's ticket creation endpoint does **not** accept comments inline. You must: 1. Create the ticket via `POST /v3/tickets` 2. Capture the returned ticket `id` from `resp.json() ["data"]["id"]` 3. For each comment/reply in your JSON, call the Ticket Comments API sequentially ```bash curl --request POST \ --url 'https://api.surveysparrow.com/v3/ticket-comments' \ --header 'Authorization: Bearer YOUR_ACCESS_TOKEN' \ --header 'Content-Type: application/json' \ --data '{ "ticket_id": 12345, "body": "Reproduced on staging. Escalating to backend team." }' ``` Preserve comment order by inserting them sequentially per ticket. Do not parallelize comment insertion within a single ticket. ### The Author Attribution Problem When writing historical comments via API, the system attributes the comment to the API key owner (usually an admin) rather than the original agent or customer. The Comments API also does not allow setting `created_at`. To preserve historical accuracy, prepend metadata to the comment body: `" [Originally posted by [email protected] on 2025-11-03T15:01:00Z] Reproduced on staging."` This ensures agents reading historical tickets have context about who sent the message and when. Document this convention in your migration runbook so support teams know how to read historical ticket threads. ## Handling Attachments SurveySparrow's single ticket creation endpoint accepts attachments via `multipart/form-data`. The constraints are strict: - **Allowed file types:** pdf, png, jpeg, mp3, csv, wav - **Max file size:** 15 MB per file - **Batch API does not support attachments** If your JSON references attachments by URL, your migration script must download each file locally, validate its type and size, then attach it via multipart upload during ticket creation. If the JSON contains Base64-encoded strings, decode into binary before uploading. This significantly increases memory usage and API overhead — process attachments one file at a time rather than loading all decoded files into memory simultaneously. Files outside the allowed types (e.g., .docx, .xlsx, .zip) **cannot be uploaded**. Your options: convert where possible (e.g., .xlsx → .csv), or store the original URL in a custom Text field for manual retrieval. Document every unsupported file type encountered during migration in a conversion log for post-migration follow-up. ## Rate Limits and Throttling SurveySparrow enforces API rate limits that vary by plan tier. The exact per-minute limits are not published in the public API documentation and differ between Basic, Business, Professional, and Enterprise plans. Contact SurveySparrow support to request the specific limits for your account tier before planning concurrency levels. When you exceed the limit, SurveySparrow returns a `429 Too Many Requests` status code. Implement exponential backoff with jitter to avoid retry storms when multiple workers hit the limit simultaneously: ```python import time import random import requests def make_api_call(url, payload, headers, max_retries=5): retries = 0 backoff = 1 # seconds while retries < max_retries: response = requests.post(url, json=payload, headers=headers) if response.status_code in (200, 201): return response.json() if response.status_code == 429: jitter = random.uniform(0, backoff * 0.5) sleep_time = backoff + jitter print(f"Rate limited. Sleeping {sleep_time:.1f}s (retry {retries+1}/{max_retries})") time.sleep(sleep_time) backoff = min(backoff * 2, 60) # cap at 60 seconds retries += 1 continue raise Exception(f"API Error: {response.status_code} - {response.text}") raise Exception(f"Max retries exceeded for {url}") ``` **Practical starting points:** - **Sequential migrations:** 1 request per second is a safe starting point for any plan tier - **Concurrent migrations:** Begin at 3 workers; observe for 10 minutes before increasing - **Large migrations (10,000+ tickets):** Contact SurveySparrow support to request a temporary rate limit increase before starting, or use the batch endpoint for the ticket creation phase ## Error Handling and Idempotency The SurveySparrow ticket API does **not** have built-in idempotency keys. If your script retries a failed request and the original actually succeeded, you get duplicate tickets. If your script crashes after migrating 45,000 out of 100,000 tickets, you cannot restart from the beginning without creating 45,000 duplicates. Your script must track its own state. Use SQLite for migrations of any significant size — it handles concurrent writes safely and survives process crashes, unlike a JSON state file. ```python import json import sqlite3 import requests import time def init_state_db(db_path="migration_state.db"): conn = sqlite3.connect(db_path) conn.execute(""" CREATE TABLE IF NOT EXISTS migrations ( source_id TEXT PRIMARY KEY, ss_ticket_id INTEGER, status TEXT, migrated_at TEXT ) """) conn.commit() return conn def is_migrated(conn, source_id): row = conn.execute( "SELECT ss_ticket_id FROM migrations WHERE source_id = ? AND status = 'success'", (source_id,) ).fetchone() return row[0] if row else None def record_migration(conn, source_id, ss_ticket_id, status): conn.execute( """INSERT OR REPLACE INTO migrations (source_id, ss_ticket_id, status, migrated_at) VALUES (?, ?, ?, datetime('now'))""", (source_id, ss_ticket_id, status) ) conn.commit() def create_ticket(ticket_data, conn, base_url, headers, priority_map, status_map): source_id = str(ticket_data["id"]) existing_ss_id = is_migrated(conn, source_id) if existing_ss_id: print(f"Skipping {source_id} — already migrated as {existing_ss_id}") return existing_ss_id payload = { "subject": ticket_data["title"][:200], "description": ticket_data.get("body", ""), "priority": priority_map.get(ticket_data.get("priority", "medium").lower()), "status": status_map.get(ticket_data.get("status", "open").lower()), "email": ticket_data.get("reporter_email", "") } resp = requests.post( f"{base_url}/v3/tickets", headers=headers, data=payload ) if resp.status_code == 200: ss_id = resp.json()["data"]["id"] record_migration(conn, source_id, ss_id, "success") return ss_id elif resp.status_code == 429: time.sleep(10) return create_ticket(ticket_data, conn, base_url, headers, priority_map, status_map) else: record_migration(conn, source_id, None, f"failed:{resp.status_code}") print(f"FAILED {source_id}: {resp.status_code} {resp.text}") return None ``` On any network timeout, query `GET /v3/tickets` (filtered by subject or requester) before retrying to verify whether the ticket was actually created. This prevents creating duplicates on transient network failures where the API call succeeded but the response was lost. ## Common Edge Cases and Failure Modes **Subject exceeds 200 characters.** The API rejects the request. Truncate proactively and log the original value in a custom Text field if full preservation matters. **Priority/status integer IDs not resolved before migration.** If you pass an integer that doesn't correspond to a valid option in your SurveySparrow instance, the API may reject the ticket or assign a default silently. Always resolve via `build_field_maps()` before starting. **Custom field `internal_name` case mismatch.** A field created as `Original_Created_Date` must be referenced exactly that way. Mismatches produce no error — the data is silently dropped. Validate your field names against the Ticket Fields API response before loading. **Contacts with duplicate emails.** SurveySparrow de-duplicates contacts by email. If your JSON has multiple requesters with the same email but different names, only the first contact record persists. Audit for email duplicates in your JSON before contact pre-creation. **Null values in required fields.** SurveySparrow may reject payloads where expected string fields are passed as `null`. Convert `null` to empty strings during the mapping phase. **Character encoding issues.** Ensure your script handles UTF-8 correctly. Strip illegal control characters (Unicode code points U+0000–U+001F, excluding tab, newline, and carriage return) or unsupported emojis that might cause a `400 Bad Request` response. **HTML in descriptions.** If your JSON contains raw HTML from a previous system, ensure it does not contain broken tags or content that SurveySparrow's input filtering may block. **Original timestamps are lost.** SurveySparrow sets `created_at` at API call time. For compliance or reporting requirements depending on original creation dates, store them in a custom Date field. Document this in your migration runbook and communicate it to stakeholders before migration begins — it is consistently the most common post-migration complaint. **Pagination skipped on list endpoints.** `GET /v3/contacts`, `GET /v3/users`, and `GET /v3/teams` all return paginated results. Fetching only the first page produces an incomplete lookup map, causing failed agent assignments and duplicate contact creation. Use the `get_all_contacts()` pattern shown above for every list retrieval. **Batch API partial failures invisible without polling.** The batch endpoint returns `202 Accepted` regardless of whether individual records will fail. Individual failures are only visible in the status token response. Always poll to completion and re-submit failures via the single ticket API for granular error detail. **Source field values assumed without resolution.** The `source` field requires account-specific integer IDs, just like `priority` and `status`. Do not assume any mapping — resolve via the Ticket Fields API using `build_field_maps()`. ## Migration Validation Checklist After loading, verify before going live: - [ ] **Record count:** Total tickets in SurveySparrow matches source JSON count - [ ] **Spot-check 5–10% of tickets:** Subject, description, priority, status, requester all match - [ ] **Custom fields populated:** Verify non-default fields contain expected values; check for silently dropped keys - [ ] **Comments attached:** For tickets with threaded replies, confirm comment count and insertion order - [ ] **Attachments present:** Verify file count per ticket on a sample - [ ] **No duplicates:** Search for tickets with identical subjects from the same requester - [ ] **Agent assignments correct:** Confirm `assignee_id` resolved to the right user - [ ] **Timestamps documented:** Original `created_at` values stored in custom field if required - [ ] **Failed record log reviewed:** Check SQLite `migrations` table for any `status LIKE 'failed:%'` rows and resolve - [ ] **Batch status tokens polled to completion:** No tokens left in `processing` state ## When Not to Use This Approach Direct JSON-to-API migration works for most scenarios, but not all: - **If your JSON exceeds 100,000 tickets with attachments and comments**, use the three-phase approach: batch-load tickets first, add comments in a second concurrent pass, then upload attachments in a third pass. The sequential single-ticket path will take days. - **If you need bi-directional sync**, not just a one-time migration, you need an integration layer — not a migration script. See our guide on [data mapping for help desk migrations](https://clonepartner.com/blog/blog/your-ultimate-guide-to-data-mapping-for-a-flawless-help-desk-data-migration-with-csv-templates/). - **If your JSON contains data from a platform with a native SurveySparrow integration** (e.g., HubSpot, Salesforce, Jira), check if the built-in connector can handle ongoing sync instead of a manual migration. ## Summary: Critical Facts for JSON-to-SurveySparrow Migrations - **No native JSON importer.** All ticket creation goes through the API. - **Priority, status, and source are account-specific integer IDs.** Resolve them dynamically from the Ticket Fields API before writing transformation logic. - **`created_at` is not settable.** Store original timestamps in a custom Date field. - **Custom field key mismatches are silently dropped.** Field `internal_name` values are case-sensitive; validate before loading. - **Comments require a separate API call per comment** after ticket creation, using the ticket ID from the creation response (`resp.json() ["data"]["id"]`). - **The batch API is asynchronous** and returns a polling token, not ticket IDs. Always poll to completion and re-submit failures via the single API. - **All list endpoints are paginated.** Contacts, users, and teams require pagination loops, not single-call assumptions. - **Attachments:** pdf, png, jpeg, mp3, csv, wav only; 15 MB max; single API only. - **Rate limits vary by plan tier** and are not publicly documented. Start at 1 req/sec; request limit details from SurveySparrow support for your account. - **Use SQLite for idempotency state**, not a JSON file — it handles concurrent writes and process crashes safely. For deeper technical context on SurveySparrow migrations, see our guides on [SurveySparrow to Zendesk Migration](https://clonepartner.com/blog/blog/surveysparrow-to-zendesk-migration-the-technical-guide/) and [SurveySparrow Ticket Management to Missive Migration](https://clonepartner.com/blog/blog/surveysparrow-ticket-management-to-missive-migration-technical-guide/). > Need help migrating JSON data into SurveySparrow? Our engineers build custom migration pipelines — including field mapping, contact resolution, comment threading, and validation — so you don't have to. Book a 30-minute call to scope your migration. > > [Talk to us](https://cal.com/clonepartner/meet?duration=30) ## Frequently asked questions ### Can I import JSON files directly into SurveySparrow Ticket Management? No. SurveySparrow has no built-in JSON import for tickets. You must parse your JSON and load tickets through the SurveySparrow v3 REST API using POST /v3/tickets (single) or POST /v3/tickets/batch (bulk). CSV import is available for contacts only, not tickets. ### What fields are required to create a ticket via the SurveySparrow API? Three fields are required: subject (string, max 200 characters), priority (numeric ID), and status (numeric ID). The numeric IDs for priority and status are account-specific — retrieve them via the Ticket Fields API before mapping your data. ### Can I set the original created_at date when migrating tickets to SurveySparrow? No. The SurveySparrow ticket API does not allow setting created_at or updated_at. These timestamps are set automatically at the time of the API call. To preserve original dates, store them in a custom Date field. ### What is the difference between the single and batch ticket creation API in SurveySparrow? POST /v3/tickets is synchronous, returns the ticket ID immediately, supports attachments via multipart/form-data, and allows parent-child linking. POST /v3/tickets/batch is asynchronous (returns a status token), does not support attachments, but offers higher throughput for simple ticket imports. ### How do I prevent duplicate tickets if my migration script fails? Implement an idempotent pipeline by maintaining a local state database mapping source ticket IDs to SurveySparrow ticket IDs. Before processing any record, check whether it has already been migrated. On network timeouts, query the SurveySparrow API to verify ticket creation before retrying.