Whatever message this page gives is out now! Go check it out!
DeserializeJSON
Last update:
May 18, 2026
Description
Converts a JSON (JavaScript Object Notation) string data representation into CFML data, such as a CFML structure or array.
Returns
The data value in ColdFusion format: a structure, array, query, or simple value.
Category
Syntax
DeserializeJSON(JSONVar[, strictMapping, useCustomSerializer, preserveCaseForStructKey])See also
IsJSON, SerializeJSON, cfajaxproxy , Using Ajax Data and Development Features in the Developing ColdFusion Applications, http://www.json.org
History
- ColdFusion (2025 release):
- Added the parameter preserveCaseForStructKey. In earlier versions, the case-sensitive struct wasn't being maintained when executing this function. In this release, the casing of the struct keys would be maintained.
- Allows deserializing a JSON object to a query.See example for more information.
- ColdFusion 11: Added the parameter useCustomSerializer.
- ColdFusion 8: Added this function.
Parameters
Parameter | Description |
JSONVar | A valid JSON string expression. |
strictMapping | A Boolean or String that specifies whether to convert the JSON strictly, as follows:
|
| useCustomSerializer | true/false. Whether to use the customSerializer or not. The default value is true. The custom serializer will always be used for deserialization. If false, the JSON deserialization will use the default ColdFusion behavior. |
| preserveCaseForStructKey | Maintain the casing of struct keys. True/False. |
Usage
This function is useful any time a ColdFusion page receives data as JSON strings. It is useful in ColdFusion applications that use Ajax to represent data on the client browser, and lets you consume JSON format data from the client-side Ajax JavaScript. You can also use it on pages that get data from services that supply data as JavaScript function calls with JSON parameters. The DeserializeJSON function converts each JSON data type directly into the equivalent ColdFusion data type, as follows:
- If the strictMapping parameter is true (the default), all JSON objects become CFML structures.
- If the strictMapping parameter is false, ColdFusion determines if JSON objects represent queries and, if so, converts them to ColdFusion query object. All other JSON objects become ColdFusion structures. The DeserializeJSON function recognizes a JSON structure as a query and converts it properly if the structure uses either of the two query representation formats described in the SerializeJSON reference.
- JSON Arrays, Strings, and Numbers become ColdFusion arrays, strings, and numbers.
- Since ColdFfusion 10, JSON null values become undefined.
- JSON string representations of a dates and times remain strings, but ColdFusion date/time handling code can recognize them as representing dates and times.
Note: Bracket and dot notation also work. The index value changes depending on the order of the item.
Example
The following example parses JSON data and converts the JSON data into a ColdFusion structure.
<cfscript>
record=deserializeJSON(
'{
"firstname": "John",
"lastname": "Smith",
"age": "36",
"address":{
"number":"12345",
"street":"my_street",
"city":"any city"
}
}'
);
writeOutput(record.firstname & " ");
writeOutput(record.lastname & " ");
writeOutput(record.address.number & " ");
writeOutput(record.address.street & " ");
</cfscript>Expected output:
John Smith 12345 my_street
Example- Using preserveCaseForStructKey
<cfscript>
jsonRecord='{
"name": "Sherlock",
"NAME": "Holmes",
"age": "36",
"address":{
"Number":"221B",
"Street":"Baker Street",
"City":"London"
}
}'
deserializedRecord=deserializeJSON(json=jsonRecord,preserveCaseForStructKey=false)
writeDump(deserializedRecord)
// preserveCaseForStructKey=TRUE or FALSE. FALSE by default
writeOutput("<br/>" & "Output with preserveCaseForStructKey=TRUE" & "<br/>")
deserializedRecordCaseStructKey=deserializeJSON(json=jsonRecord,preserveCaseForStructKey=true)
writeDump(deserializedRecordCaseStructKey)
</cfscript>Output
Example- deserializing a JSON object to a query
The function includes “query” as value to return the JSON object as query.
For example,
<cfscript>
myJSON=
'
[
{
"id": "1",
"name": "One",
"amount": "15"
},
{
"id": "2",
"name": "Two",
"amount": "18"
},
{
"id": "3",
"name": "Three",
"amount": "32"
}
]
'
q=deserializeJSON(myJSON,"query")
writeDump(q)
</cfscript>Output
Real-world use cases of the DeserializeJSON function
eCommerce shopping cart processing
Your online store needs to process shopping cart data from mobile apps, web browsers, and partner integrations. Customers add items, apply discounts, and checkout, all sending JSON data to your ColdFusion backend.
How it works:
- Customer uses your mobile app to add 3 items to cart
- App sends JSON with product IDs, quantities, customer info
- Your system calculates totals, applies member discounts, adds shipping
- Order is created and fulfillment process begins
<cfscript>
function processShoppingCart(cartJsonData, customerType = "regular") {
try {
// Deserialize cart JSON with validation
cart = DeserializeJSON(cartJsonData);
// Validate cart structure
if (!structKeyExists(cart, "customer") || !structKeyExists(cart, "items")) {
return {
success: false,
error: "Invalid cart structure",
code: "CART_INVALID_STRUCTURE"
};
}
if (!isArray(cart.items) || arrayLen(cart.items) == 0) {
return {
success: false,
error: "Cart is empty or items not in array format",
code: "CART_EMPTY"
};
}
// Initialize cart processing
processedCart = {
customer: {
id: cart.customer.id ?: createUUID(),
name: cart.customer.name ?: "Guest Customer",
email: cart.customer.email ?: "",
membershipLevel: cart.customer.membershipLevel ?: "standard",
address: cart.customer.address ?: {}
},
items: [],
pricing: {
subtotal: 0,
discountTotal: 0,
taxableAmount: 0,
taxAmount: 0,
shippingCost: 0,
finalTotal: 0
},
discounts: [],
shipping: {},
metadata: {
processedAt: now(),
currency: cart.currency ?: "USD",
source: cart.source ?: "web"
}
};
// Process each cart item
for (itemIndex = 1; itemIndex <= arrayLen(cart.items); itemIndex++) {
item = cart.items[itemIndex];
// Validate item structure
if (!structKeyExists(item, "productId") || !structKeyExists(item, "price") || !structKeyExists(item, "quantity")) {
continue; // Skip invalid items
}
// Calculate item pricing
unitPrice = val(item.price);
quantity = val(item.quantity);
lineSubtotal = unitPrice * quantity;
// Apply item-level discounts
itemDiscount = 0;
discountReason = "";
// Bulk discount (5+ items)
if (quantity >= 5) {
itemDiscount = lineSubtotal * 0.05; // 5% bulk discount
discountReason = "Bulk discount (5%)";
}
// Membership discounts
switch (processedCart.customer.membershipLevel) {
case "premium":
memberDiscount = lineSubtotal * 0.10; // 10% premium discount
if (memberDiscount > itemDiscount) {
itemDiscount = memberDiscount;
discountReason = "Premium member discount (10%)";
}
break;
case "gold":
memberDiscount = lineSubtotal * 0.15; // 15% gold discount
if (memberDiscount > itemDiscount) {
itemDiscount = memberDiscount;
discountReason = "Gold member discount (15%)";
}
break;
case "vip":
memberDiscount = lineSubtotal * 0.20; // 20% VIP discount
if (memberDiscount > itemDiscount) {
itemDiscount = memberDiscount;
discountReason = "VIP member discount (20%)";
}
break;
}
lineFinalPrice = lineSubtotal - itemDiscount;
// Build processed item
processedItem = {
productId: item.productId,
productName: item.productName ?: "Unknown Product",
sku: item.sku ?: "",
category: item.category ?: "general",
unitPrice: unitPrice,
quantity: quantity,
subtotal: lineSubtotal,
discount: itemDiscount,
discountReason: discountReason,
finalPrice: lineFinalPrice,
taxable: item.taxable ?: true,
weight: val(item.weight ?: 0),
dimensions: item.dimensions ?: {}
};
arrayAppend(processedCart.items, processedItem);
// Update cart totals
processedCart.pricing.subtotal += lineSubtotal;
processedCart.pricing.discountTotal += itemDiscount;
// Track discounts applied
if (itemDiscount > 0) {
arrayAppend(processedCart.discounts, {
productId: item.productId,
discountAmount: itemDiscount,
reason: discountReason
});
}
}
// Calculate taxable amount
processedCart.pricing.taxableAmount = processedCart.pricing.subtotal - processedCart.pricing.discountTotal;
// Apply cart-level promotions
cartLevelDiscount = 0;
// Free shipping promotion
if (processedCart.pricing.taxableAmount >= 100) {
processedCart.shipping.freeShippingApplied = true;
processedCart.shipping.freeShippingReason = "Orders over $100 qualify for free shipping";
}
// First-time customer discount
if (structKeyExists(cart.customer, "isFirstTime") && cart.customer.isFirstTime) {
cartLevelDiscount = min(processedCart.pricing.taxableAmount * 0.10, 50); // 10% up to $50
arrayAppend(processedCart.discounts, {
type: "cart_level",
discountAmount: cartLevelDiscount,
reason: "First-time customer discount (10%)"
});
}
processedCart.pricing.discountTotal += cartLevelDiscount;
processedCart.pricing.taxableAmount -= cartLevelDiscount;
// Calculate tax (example: 8.5% sales tax)
taxRate = 0.085;
if (structKeyExists(cart.customer, "address") && structKeyExists(cart.customer.address, "state")) {
// State-specific tax rates
stateTaxRates = {
"CA": 0.0975,
"NY": 0.08,
"TX": 0.0625,
"FL": 0.06,
"WA": 0.065
};
if (structKeyExists(stateTaxRates, cart.customer.address.state)) {
taxRate = stateTaxRates[cart.customer.address.state];
}
}
processedCart.pricing.taxAmount = processedCart.pricing.taxableAmount * taxRate;
// Calculate shipping cost
if (!processedCart.shipping.freeShippingApplied) {
totalWeight = 0;
for (item in processedCart.items) {
totalWeight += item.weight * item.quantity;
}
// Weight-based shipping
if (totalWeight <= 1) {
processedCart.pricing.shippingCost = 5.99;
} else if (totalWeight <= 5) {
processedCart.pricing.shippingCost = 9.99;
} else if (totalWeight <= 10) {
processedCart.pricing.shippingCost = 14.99;
} else {
processedCart.pricing.shippingCost = 19.99;
}
// Expedited shipping option
if (structKeyExists(cart, "shipping") && cart.shipping.expedited) {
processedCart.pricing.shippingCost *= 2; // Double for expedited
processedCart.shipping.expedited = true;
processedCart.shipping.estimatedDelivery = "1-2 business days";
} else {
processedCart.shipping.estimatedDelivery = "5-7 business days";
}
} else {
processedCart.pricing.shippingCost = 0;
}
// Calculate final total
processedCart.pricing.finalTotal = processedCart.pricing.taxableAmount +
processedCart.pricing.taxAmount +
processedCart.pricing.shippingCost;
// Add cart metadata
processedCart.metadata.itemCount = arrayLen(processedCart.items);
processedCart.metadata.totalQuantity = 0;
for (item in processedCart.items) {
processedCart.metadata.totalQuantity += item.quantity;
}
return {
success: true,
cart: processedCart,
summary: {
itemCount: processedCart.metadata.itemCount,
totalQuantity: processedCart.metadata.totalQuantity,
subtotal: processedCart.pricing.subtotal,
discounts: processedCart.pricing.discountTotal,
tax: processedCart.pricing.taxAmount,
shipping: processedCart.pricing.shippingCost,
total: processedCart.pricing.finalTotal
}
};
} catch (any e) {
return {
success: false,
error: "Cart processing failed: " & e.message,
code: "CART_PROCESSING_ERROR",
detail: e.detail ?: ""
};
}
}
// =============================================================================
// SAMPLE USAGE
// =============================================================================
// Sample complex cart JSON
cartJson = '{
"customer": {
"id": "CUST-12345",
"name": "Sarah Johnson",
"email": "sarah@example.com",
"membershipLevel": "premium",
"isFirstTime": false,
"address": {
"street": "123 Main St",
"city": "Los Angeles",
"state": "CA",
"zipCode": "90210"
}
},
"items": [
{
"productId": "PROD-001",
"productName": "Premium Laptop",
"sku": "LP-001",
"category": "electronics",
"price": 1299.99,
"quantity": 1,
"weight": 4.5,
"taxable": true
},
{
"productId": "PROD-002",
"productName": "Wireless Mouse",
"sku": "MS-002",
"category": "accessories",
"price": 49.99,
"quantity": 2,
"weight": 0.3,
"taxable": true
},
{
"productId": "PROD-003",
"productName": "USB-C Cable",
"sku": "CB-003",
"category": "accessories",
"price": 19.99,
"quantity": 3,
"weight": 0.2,
"taxable": true
}
],
"currency": "USD",
"source": "mobile_app",
"shipping": {
"expedited": false
}
}';
// Execute cart processing
cartResult = processShoppingCart(cartJson);
// Display results
if (cartResult.success) {
writeOutput("<h2>🛒 Shopping Cart Processing Results</h2>");
writeOutput("<h3>Cart Summary</h3>");
writeOutput("<p><strong>Items:</strong> " & cartResult.summary.itemCount & "</p>");
writeOutput("<p><strong>Total Quantity:</strong> " & cartResult.summary.totalQuantity & "</p>");
writeOutput("<p><strong>Subtotal:</strong> $" & numberFormat(cartResult.summary.subtotal, "999.99") & "</p>");
writeOutput("<p><strong>Discounts:</strong> -$" & numberFormat(cartResult.summary.discounts, "999.99") & "</p>");
writeOutput("<p><strong>Tax:</strong> $" & numberFormat(cartResult.summary.tax, "999.99") & "</p>");
writeOutput("<p><strong>Shipping:</strong> $" & numberFormat(cartResult.summary.shipping, "999.99") & "</p>");
writeOutput("<p><strong>Final Total:</strong> $" & numberFormat(cartResult.summary.total, "999.99") & "</p>");
writeOutput("<h3>Customer Information</h3>");
writeOutput("<p><strong>Name:</strong> " & cartResult.cart.customer.name & "</p>");
writeOutput("<p><strong>Membership:</strong> " & cartResult.cart.customer.membershipLevel & "</p>");
writeOutput("<p><strong>Email:</strong> " & cartResult.cart.customer.email & "</p>");
} else {
writeOutput("<h2>❌ Cart Processing Error</h2>");
writeOutput("<p><strong>Error:</strong> " & cartResult.error & "</p>");
writeOutput("<p><strong>Code:</strong> " & cartResult.code & "</p>");
}
</cfscript>CRM integration
Your lead generation system integrates with Salesforce, HubSpot, and other CRMs that require exact field name casing. Web form data must be mapped correctly to avoid integration failures.
How it works:
- Prospect fills out "Request Demo" form on your website
- Form data includes firstName, lastName, companyName (case-sensitive)
- System sends lead data to Salesforce API with exact field names
- Lead appears instantly in sales team's CRM dashboard
<cfscript>
// =============================================================================
// SAMPLE LEAD JSON DATA
// =============================================================================
leadJson = '{
"firstName": "Michael",
"lastName": "Chen",
"emailAddress": "michael.chen@techsolutions.com",
"phoneNumber": "5551234567",
"companyName": "Tech Solutions Inc",
"jobTitle": "VP Engineering",
"industry": "Software",
"employeeCount": 250,
"annualRevenue": 5000000,
"address": {
"street": "123 Innovation Drive",
"city": "San Francisco",
"state": "CA",
"zipCode": "94105"
},
"interestLevel": "high",
"budgetRange": "50000-100000"
}';
try {
// Test basic deserialization
lead = DeserializeJSON(leadJson);
writeOutput("<p class='success'><strong>✅ JSON Deserialization:</strong> SUCCESS</p>");
writeOutput("<p><strong>First Name:</strong> " & lead.firstName & "</p>");
writeOutput("<p><strong>Last Name:</strong> " & lead.lastName & "</p>");
writeOutput("<p><strong>Company:</strong> " & lead.companyName & "</p>");
writeOutput("<p><strong>Email:</strong> " & lead.emailAddress & "</p>");
// Test case-sensitive deserialization
leadCaseSensitive = DeserializeJSON(leadJson, true, true, true);
writeOutput("<p class='success'><strong>✅ Case-Sensitive Deserialization:</strong> SUCCESS</p>");
} catch (any e) {
writeOutput("<p class='error'><strong>❌ JSON Deserialization:</strong> FAILED</p>");
writeOutput("<p class='error'><strong>Error:</strong> " & e.message & "</p>");
}
// =============================================================================
// LEAD PROCESSING FUNCTION (SIMPLIFIED)
// =============================================================================
function processLead(leadData) {
result = {
success: false,
processedData: {},
salesforceRecord: {},
errors: [],
warnings: []
};
try {
// Basic validation
if (!structKeyExists(leadData, "firstName") || len(trim(leadData.firstName)) == 0) {
arrayAppend(result.errors, "First name is required");
}
if (!structKeyExists(leadData, "lastName") || len(trim(leadData.lastName)) == 0) {
arrayAppend(result.errors, "Last name is required");
}
if (!structKeyExists(leadData, "emailAddress") || len(trim(leadData.emailAddress)) == 0) {
arrayAppend(result.errors, "Email address is required");
}
if (!structKeyExists(leadData, "companyName") || len(trim(leadData.companyName)) == 0) {
arrayAppend(result.errors, "Company name is required");
}
// If no errors, process the data
if (arrayLen(result.errors) == 0) {
// Clean and format data
result.processedData = {
firstName: uCase(left(leadData.firstName, 1)) & lCase(mid(leadData.firstName, 2, len(leadData.firstName))),
lastName: uCase(left(leadData.lastName, 1)) & lCase(mid(leadData.lastName, 2, len(leadData.lastName))),
email: lCase(leadData.emailAddress),
company: leadData.companyName,
jobTitle: leadData.jobTitle ?: "",
industry: leadData.industry ?: "",
phone: leadData.phoneNumber ?: ""
};
// Calculate lead score
score = 0;
if (structKeyExists(leadData, "employeeCount") && val(leadData.employeeCount) > 100) {
score += 25;
}
if (structKeyExists(leadData, "annualRevenue") && val(leadData.annualRevenue) > 1000000) {
score += 30;
}
if (structKeyExists(leadData, "jobTitle") &&
(findNoCase("VP", leadData.jobTitle) || findNoCase("Director", leadData.jobTitle))) {
score += 20;
}
if (structKeyExists(leadData, "interestLevel") && leadData.interestLevel == "high") {
score += 25;
}
result.processedData.leadScore = score;
// Determine status based on score
if (score >= 75) {
result.processedData.status = "Hot";
} else if (score >= 50) {
result.processedData.status = "Warm";
} else if (score >= 25) {
result.processedData.status = "Qualified";
} else {
result.processedData.status = "New";
}
// Create Salesforce record structure
result.salesforceRecord = {
"FirstName": result.processedData.firstName,
"LastName": result.processedData.lastName,
"Email": result.processedData.email,
"Company": result.processedData.company,
"Title": result.processedData.jobTitle,
"Industry": result.processedData.industry,
"Phone": result.processedData.phone,
"Status": result.processedData.status,
"Lead_Score__c": result.processedData.leadScore
};
result.success = true;
}
} catch (any e) {
arrayAppend(result.errors, "Processing failed: " & e.message);
}
return result;
}
// =============================================================================
// EXECUTE LEAD PROCESSING
// =============================================================================
writeOutput("<h2>🏢 Lead Processing Results</h2>");
try {
// Process the lead
processedLead = processLead(lead);
if (processedLead.success) {
writeOutput("<p class='success'><strong>✅ Lead Processing:</strong> SUCCESS</p>");
writeOutput("<h3>📋 Processed Lead Information</h3>");
writeOutput("<table>");
writeOutput("<tr><th>Field</th><th>Value</th></tr>");
writeOutput("<tr><td>Full Name</td><td>" & processedLead.processedData.firstName & " " & processedLead.processedData.lastName & "</td></tr>");
writeOutput("<tr><td>Email</td><td>" & processedLead.processedData.email & "</td></tr>");
writeOutput("<tr><td>Company</td><td>" & processedLead.processedData.company & "</td></tr>");
writeOutput("<tr><td>Job Title</td><td>" & processedLead.processedData.jobTitle & "</td></tr>");
writeOutput("<tr><td>Industry</td><td>" & processedLead.processedData.industry & "</td></tr>");
writeOutput("<tr><td>Phone</td><td>" & processedLead.processedData.phone & "</td></tr>");
writeOutput("<tr><td><strong>Lead Score</strong></td><td><strong>" & processedLead.processedData.leadScore & "/100</strong></td></tr>");
writeOutput("<tr><td><strong>Status</strong></td><td><strong>" & processedLead.processedData.status & "</strong></td></tr>");
writeOutput("</table>");
writeOutput("<h3>🔗 Salesforce API Record</h3>");
writeOutput("<table>");
writeOutput("<tr><th>Salesforce Field</th><th>Value</th></tr>");
for (field in processedLead.salesforceRecord) {
writeOutput("<tr><td>" & field & "</td><td>" & processedLead.salesforceRecord[field] & "</td></tr>");
}
writeOutput("</table>");
} else {
writeOutput("<p class='error'><strong>❌ Lead Processing:</strong> FAILED</p>");
if (arrayLen(processedLead.errors) > 0) {
writeOutput("<h3>Errors:</h3>");
writeOutput("<ul>");
for (error in processedLead.errors) {
writeOutput("<li class='error'>" & error & "</li>");
}
writeOutput("</ul>");
}
}
} catch (any e) {
writeOutput("<p class='error'><strong>❌ Critical Error:</strong> " & e.message & "</p>");
}
// =============================================================================
// MULTIPLE TEST CASES
// =============================================================================
// Test case 2: High-value lead
highValueJson = '{
"firstName": "Sarah",
"lastName": "Wilson",
"emailAddress": "sarah@bigcorp.com",
"companyName": "Big Corporation",
"jobTitle": "VP Sales",
"employeeCount": 500,
"annualRevenue": 10000000,
"interestLevel": "high"
}';
highValueLead = DeserializeJSON(highValueJson);
highValueResult = processLead(highValueLead);
writeOutput("<h3>Test 2: High-Value Lead</h3>");
writeOutput("<p><strong>Result:</strong> " & (highValueResult.success ? "<span class='success'>SUCCESS</span>" : "<span class='error'>FAILED</span>") & "</p>");
writeOutput("<p><strong>Lead Score:</strong> " & (highValueResult.success ? highValueResult.processedData.leadScore : "N/A") & "</p>");
writeOutput("<p><strong>Status:</strong> " & (highValueResult.success ? highValueResult.processedData.status : "N/A") & "</p>");
</cfscript>Business intelligence and reporting
Your BI system collects sales data from multiple channels (online, retail stores, B2B portals) in JSON format. This data needs to be converted to queries for analysis, trending, and executive reporting.
Why it's important
- Daily sales reports arrive as JSON arrays from 5 different systems
- Each contains transaction details, products, salespeople, regions
- Data gets converted to ColdFusion queries for analysis
- Executive dashboard shows real-time metrics and trends
<cfscript>
function processSalesAnalytics(salesJsonData, reportConfig = {}) {
try {
// Deserialize JSON array directly to ColdFusion query for performance
salesQuery = DeserializeJSON(salesJsonData, "query");
// Initialize analytics result structure
analytics = {
summary: {
totalSales: 0,
transactionCount: 0,
averageOrderValue: 0,
topPerformers: [],
salesByRegion: {},
salesByProduct: {},
monthlyTrends: {},
conversionMetrics: {}
},
detailedAnalytics: {
salesTeamPerformance: [],
productPerformance: [],
regionalAnalysis: [],
customerSegments: []
},
alerts: [],
recommendations: [],
metadata: {
reportGeneratedAt: now(),
recordsProcessed: salesQuery.recordCount,
dateRange: {
startDate: "",
endDate: ""
}
}
};
// Validate query structure
requiredColumns = ["transactionId", "saleDate", "salesperson", "customerId", "product", "amount", "quantity"];
missingColumns = [];
for (col in requiredColumns) {
if (!listFindNoCase(salesQuery.columnList, col)) {
arrayAppend(missingColumns, col);
}
}
if (arrayLen(missingColumns) > 0) {
return {
success: false,
error: "Missing required columns: " & arrayToList(missingColumns),
code: "INVALID_DATA_STRUCTURE"
};
}
// Initialize tracking variables
salesBySalesperson = {};
salesByProduct = {};
salesByRegion = {};
salesByMonth = {};
customerData = {};
dailySales = {};
// Process each sales record
for (row = 1; row <= salesQuery.recordCount; row++) {
// Extract and validate row data
transactionId = salesQuery.transactionId[row];
saleDate = parseDateTime(salesQuery.saleDate[row]);
salesperson = salesQuery.salesperson[row];
customerId = salesQuery.customerId[row];
productName = salesQuery.product[row];
amount = val(salesQuery.amount[row]);
quantity = val(salesQuery.quantity[row]);
// Additional optional fields (with safe access)
region = "Unknown";
customerType = "Standard";
channel = "Direct";
try {
if (listFindNoCase(salesQuery.columnList, "region")) {
region = salesQuery.region[row];
}
} catch (any e) {
region = "Unknown";
}
try {
if (listFindNoCase(salesQuery.columnList, "customerType")) {
customerType = salesQuery.customerType[row];
}
} catch (any e) {
customerType = "Standard";
}
try {
if (listFindNoCase(salesQuery.columnList, "channel")) {
channel = salesQuery.channel[row];
}
} catch (any e) {
channel = "Direct";
}
// Validate data quality
if (amount <= 0 || quantity <= 0) {
arrayAppend(analytics.alerts, {
type: "data_quality",
severity: "medium",
message: "Invalid amount or quantity in transaction " & transactionId,
transactionId: transactionId
});
continue; // Skip invalid records
}
// Update summary totals
analytics.summary.totalSales += amount;
analytics.summary.transactionCount++;
// Track date range
if (analytics.metadata.dateRange.startDate == "" || saleDate < analytics.metadata.dateRange.startDate) {
analytics.metadata.dateRange.startDate = saleDate;
}
if (analytics.metadata.dateRange.endDate == "" || saleDate > analytics.metadata.dateRange.endDate) {
analytics.metadata.dateRange.endDate = saleDate;
}
// Salesperson performance tracking
if (!structKeyExists(salesBySalesperson, salesperson)) {
salesBySalesperson[salesperson] = {
totalSales: 0,
transactionCount: 0,
productsScore: {},
avgOrderValue: 0,
region: region,
customerTypes: {}
};
}
salesBySalesperson[salesperson].totalSales += amount;
salesBySalesperson[salesperson].transactionCount++;
// Track customer types per salesperson
if (!structKeyExists(salesBySalesperson[salesperson].customerTypes, customerType)) {
salesBySalesperson[salesperson].customerTypes[customerType] = 0;
}
salesBySalesperson[salesperson].customerTypes[customerType]++;
// Product performance tracking
if (!structKeyExists(salesByProduct, productName)) {
salesByProduct[productName] = {
totalRevenue: 0,
totalQuantity: 0,
transactionCount: 0,
averagePrice: 0,
topSalesperson: "",
topSalesAmount: 0
};
}
salesByProduct[productName].totalRevenue += amount;
salesByProduct[productName].totalQuantity += quantity;
salesByProduct[productName].transactionCount++;
// Track top salesperson per product
if (amount > salesByProduct[productName].topSalesAmount) {
salesByProduct[productName].topSalesperson = salesperson;
salesByProduct[productName].topSalesAmount = amount;
}
// Regional analysis
if (!structKeyExists(salesByRegion, region)) {
salesByRegion[region] = {
totalSales: 0,
transactionCount: 0,
uniqueCustomers: {},
topProducts: {},
channels: {}
};
}
salesByRegion[region].totalSales += amount;
salesByRegion[region].transactionCount++;
salesByRegion[region].uniqueCustomers[customerId] = true;
// Track products by region
if (!structKeyExists(salesByRegion[region].topProducts, productName)) {
salesByRegion[region].topProducts[productName] = 0;
}
salesByRegion[region].topProducts[productName] += amount;
// Track channels by region
if (!structKeyExists(salesByRegion[region].channels, channel)) {
salesByRegion[region].channels[channel] = 0;
}
salesByRegion[region].channels[channel] += amount;
// Monthly trends
monthKey = year(saleDate) & "-" & numberFormat(month(saleDate), "00");
if (!structKeyExists(salesByMonth, monthKey)) {
salesByMonth[monthKey] = {
totalSales: 0,
transactionCount: 0,
uniqueCustomers: {},
newCustomers: 0
};
}
salesByMonth[monthKey].totalSales += amount;
salesByMonth[monthKey].transactionCount++;
salesByMonth[monthKey].uniqueCustomers[customerId] = true;
// Customer analysis
if (!structKeyExists(customerData, customerId)) {
customerData[customerId] = {
totalSpent: 0,
transactionCount: 0,
firstPurchase: saleDate,
lastPurchase: saleDate,
customerType: customerType,
preferredProducts: {},
region: region
};
}
customerData[customerId].totalSpent += amount;
customerData[customerId].transactionCount++;
if (saleDate > customerData[customerId].lastPurchase) {
customerData[customerId].lastPurchase = saleDate;
}
if (saleDate < customerData[customerId].firstPurchase) {
customerData[customerId].firstPurchase = saleDate;
}
// Track preferred products per customer
if (!structKeyExists(customerData[customerId].preferredProducts, productName)) {
customerData[customerId].preferredProducts[productName] = 0;
}
customerData[customerId].preferredProducts[productName] += amount;
}
// Calculate averages and derived metrics
analytics.summary.averageOrderValue = analytics.summary.totalSales / analytics.summary.transactionCount;
// Calculate salesperson averages
for (salesperson in salesBySalesperson) {
salesBySalesperson[salesperson].avgOrderValue =
salesBySalesperson[salesperson].totalSales / salesBySalesperson[salesperson].transactionCount;
}
// Calculate product averages
for (product in salesByProduct) {
salesByProduct[product].averagePrice =
salesByProduct[product].totalRevenue / salesByProduct[product].totalQuantity;
}
// Generate top performers list (sorted by total sales)
topPerformers = [];
for (salesperson in salesBySalesperson) {
arrayAppend(topPerformers, {
name: salesperson,
totalSales: salesBySalesperson[salesperson].totalSales,
avgOrderValue: salesBySalesperson[salesperson].avgOrderValue,
transactionCount: salesBySalesperson[salesperson].transactionCount,
region: salesBySalesperson[salesperson].region
});
}
// Sort top performers by sales amount
arraySort(topPerformers, function(a, b) {
return b.totalSales - a.totalSales;
});
analytics.summary.topPerformers = topPerformers;
// Generate alerts and recommendations
// Alert: Low performing salespeople
avgSalesPerPerson = analytics.summary.totalSales / structCount(salesBySalesperson);
for (performer in topPerformers) {
if (performer.totalSales < (avgSalesPerPerson * 0.5)) {
arrayAppend(analytics.alerts, {
type: "performance",
severity: "medium",
message: performer.name & " is performing below 50% of team average",
salesperson: performer.name,
currentSales: performer.totalSales,
teamAverage: avgSalesPerPerson
});
arrayAppend(analytics.recommendations, {
type: "training",
priority: "medium",
message: "Consider additional training or coaching for " & performer.name,
salesperson: performer.name
});
}
}
// Alert: High-value customers
for (customerId in customerData) {
customer = customerData[customerId];
if (customer.totalSpent > (analytics.summary.averageOrderValue * 10)) {
arrayAppend(analytics.recommendations, {
type: "customer_retention",
priority: "high",
message: "High-value customer " & customerId & " - ensure premium service",
customerId: customerId,
totalSpent: customer.totalSpent
});
}
}
// Populate detailed analytics
analytics.detailedAnalytics.salesTeamPerformance = topPerformers;
analytics.detailedAnalytics.productPerformance = salesByProduct;
analytics.detailedAnalytics.regionalAnalysis = salesByRegion;
// Customer segmentation
customerSegments = {
"high_value": [],
"medium_value": [],
"low_value": [],
"at_risk": []
};
for (customerId in customerData) {
customer = customerData[customerId];
// Determine customer segment based on spending
if (customer.totalSpent > (analytics.summary.averageOrderValue * 5)) {
arrayAppend(customerSegments.high_value, {
customerId: customerId,
totalSpent: customer.totalSpent,
transactionCount: customer.transactionCount
});
} else if (customer.totalSpent > analytics.summary.averageOrderValue) {
arrayAppend(customerSegments.medium_value, {
customerId: customerId,
totalSpent: customer.totalSpent,
transactionCount: customer.transactionCount
});
} else {
arrayAppend(customerSegments.low_value, {
customerId: customerId,
totalSpent: customer.totalSpent,
transactionCount: customer.transactionCount
});
}
// Check for at-risk customers (no purchases in last 90 days)
if (dateDiff("d", customer.lastPurchase, now()) > 90) {
arrayAppend(customerSegments.at_risk, {
customerId: customerId,
daysSinceLastPurchase: dateDiff("d", customer.lastPurchase, now()),
totalSpent: customer.totalSpent
});
}
}
analytics.detailedAnalytics.customerSegments = customerSegments;
// Populate summary objects
analytics.summary.salesByRegion = salesByRegion;
analytics.summary.salesByProduct = salesByProduct;
analytics.summary.monthlyTrends = salesByMonth;
return {
success: true,
analytics: analytics,
rawQuery: salesQuery
};
} catch (any e) {
return {
success: false,
error: "Sales analytics processing failed: " & e.message,
code: "ANALYTICS_PROCESSING_ERROR",
detail: e.detail ?: ""
};
}
}
// =============================================================================
// SAMPLE USAGE
// =============================================================================
// Sample complex sales JSON array
salesJson = '[
{
"transactionId": "TXN-2024-001",
"saleDate": "2024-03-01T09:30:00",
"salesperson": "Alice Johnson",
"customerId": "CUST-001",
"customerType": "Enterprise",
"product": "Enterprise Software License",
"amount": 15000.00,
"quantity": 5,
"region": "West",
"channel": "Direct Sales"
},
{
"transactionId": "TXN-2024-002",
"saleDate": "2024-03-01T11:15:00",
"salesperson": "Bob Smith",
"customerId": "CUST-002",
"customerType": "SMB",
"product": "Standard Software License",
"amount": 2500.00,
"quantity": 10,
"region": "East",
"channel": "Online"
},
{
"transactionId": "TXN-2024-003",
"saleDate": "2024-03-01T14:45:00",
"salesperson": "Alice Johnson",
"customerId": "CUST-003",
"customerType": "Enterprise",
"product": "Consulting Services",
"amount": 8000.00,
"quantity": 1,
"region": "West",
"channel": "Partner"
},
{
"transactionId": "TXN-2024-004",
"saleDate": "2024-03-02T10:20:00",
"salesperson": "Carol Davis",
"customerId": "CUST-001",
"customerType": "Enterprise",
"product": "Support Contract",
"amount": 5000.00,
"quantity": 1,
"region": "Central",
"channel": "Direct Sales"
},
{
"transactionId": "TXN-2024-005",
"saleDate": "2024-03-02T16:30:00",
"salesperson": "Bob Smith",
"customerId": "CUST-004",
"customerType": "Startup",
"product": "Starter Package",
"amount": 500.00,
"quantity": 2,
"region": "East",
"channel": "Online"
},
{
"transactionId": "TXN-2024-006",
"saleDate": "2024-03-03T13:00:00",
"salesperson": "Alice Johnson",
"customerId": "CUST-005",
"customerType": "SMB",
"product": "Standard Software License",
"amount": 3500.00,
"quantity": 7,
"region": "West",
"channel": "Reseller"
}
]';
// Test JSON deserialization first
writeOutput("<h2>🔧 JSON to Query Test</h2>");
try {
testQuery = DeserializeJSON(salesJson, "query");
writeOutput("<p><strong>Record Count:</strong> " & testQuery.recordCount & "</p>");
writeOutput("<p><strong>Column List:</strong> " & testQuery.columnList & "</p>");
// Show first record
if (testQuery.recordCount > 0) {
writeOutput("<p><strong>First Record:</strong></p>");
writeOutput("<ul>");
writeOutput("<li>Transaction ID: " & testQuery.transactionId[1] & "</li>");
writeOutput("<li>Salesperson: " & testQuery.salesperson[1] & "</li>");
writeOutput("<li>Amount: $" & numberFormat(testQuery.amount[1], "999.99") & "</li>");
writeOutput("</ul>");
}
} catch (any e) {
writeOutput("<p><strong>❌ JSON to Query conversion:</strong> FAILED</p>");
writeOutput("<p><strong>Error:</strong> " & e.message & "</p>");
}
writeOutput("<hr>");
// Execute sales analytics
writeOutput("<h2>📈 Processing Sales Analytics</h2>");
analyticsResult = processSalesAnalytics(salesJson);
// Display results
if (analyticsResult.success) {
writeOutput("<h2>📊 Sales Analytics Results</h2>");
writeOutput("<h3>Summary</h3>");
writeOutput("<p><strong>Total Sales:</strong> $" & numberFormat(analyticsResult.analytics.summary.totalSales, "999,999.99") & "</p>");
writeOutput("<p><strong>Total Transactions:</strong> " & analyticsResult.analytics.summary.transactionCount & "</p>");
writeOutput("<p><strong>Average Order Value:</strong> $" & numberFormat(analyticsResult.analytics.summary.averageOrderValue, "999.99") & "</p>");
writeOutput("<h3>Top Performers</h3>");
for (i = 1; i <= min(3, arrayLen(analyticsResult.analytics.summary.topPerformers)); i++) {
performer = analyticsResult.analytics.summary.topPerformers[i];
writeOutput("<p><strong>" & i & ". " & performer.name & ":</strong> $" & numberFormat(performer.totalSales, "999,999.99") &
" (" & performer.transactionCount & " transactions, $" & numberFormat(performer.avgOrderValue, "999.99") & " avg)</p>");
}
writeOutput("<h3>Regional Analysis</h3>");
for (region in analyticsResult.analytics.summary.salesByRegion) {
regionData = analyticsResult.analytics.summary.salesByRegion[region];
writeOutput("<p><strong>" & region & ":</strong> $" & numberFormat(regionData.totalSales, "999,999.99") &
" (" & regionData.transactionCount & " transactions)</p>");
}
writeOutput("<h3>Product Performance</h3>");
for (product in analyticsResult.analytics.summary.salesByProduct) {
productData = analyticsResult.analytics.summary.salesByProduct[product];
writeOutput("<p><strong>" & product & ":</strong> $" & numberFormat(productData.totalRevenue, "999,999.99") &
" (" & productData.totalQuantity & " units sold)</p>");
}
if (arrayLen(analyticsResult.analytics.alerts) > 0) {
writeOutput("<h3>Alerts</h3>");
for (alert in analyticsResult.analytics.alerts) {
writeOutput("<p><strong>" & uCase(alert.type) & ":</strong> " & alert.message & "</p>");
}
}
if (arrayLen(analyticsResult.analytics.recommendations) > 0) {
writeOutput("<h3>Recommendations</h3>");
for (recommendation in analyticsResult.analytics.recommendations) {
writeOutput("<p><strong>" & uCase(recommendation.type) & ":</strong> " & recommendation.message & "</p>");
}
}
} else {
writeOutput("<h2>❌ Analytics Processing Error</h2>");
if (structKeyExists(analyticsResult, "error")) {
writeOutput("<p><strong>Error:</strong> " & analyticsResult.error & "</p>");
}
if (structKeyExists(analyticsResult, "code")) {
writeOutput("<p><strong>Code:</strong> " & analyticsResult.code & "</p>");
}
if (structKeyExists(analyticsResult, "detail")) {
writeOutput("<p><strong>Detail:</strong> " & analyticsResult.detail & "</p>");
}
writeOutput("<p><strong>Debug Info:</strong> Check if JSON deserialization succeeded above.</p>");
}
</cfscript>
We're glad. Tell us how this page helped.
We're sorry. Can you tell us what didn't work for you?
Share additional feedback to help us improve.
0/255
|
