Audit log - data transformation config

Overview

The DataExtractionPattern field in EntityFieldDisplayConfig uses JSONPath expressions to extract values from enriched audit data. These patterns support:

• Case-insensitive property matching
• Placeholder replacement using {FieldName} syntax
• Filter expressions for array lookups
• Nested property access
• Fallback to original values

Pattern Categories

1. Basic Property Access

Extract a simple property from enriched data:

{
  "Field": "CustomerName",
  "DataExtractionPattern": "$.EnrichedData.CustomerName"
}

Use Case: Direct property mapping where enriched data contains the display value.

2. Simple ID Lookup

Look up a display name using an ID value:

{
  "Field": "CustomerId",
  "DataExtractionPattern": "$.EnrichedData.Customers[?(@.Id == {CustomerId})].Name"
}

How it works:

• {CustomerId} placeholder gets replaced with the actual field value (e.g., 123)
• Pattern becomes: $.EnrichedData.Customers[?(@.Id == 123)].Name
• Returns the Name property of the matching customer

Use Case: Converting foreign key IDs to readable names (customers, products, users, etc.)

3. Nested Property Access

Access deeply nested properties in the enriched data structure:

{
  "Field": "CustomerCity",
  "DataExtractionPattern": "$.EnrichedData.Customer.Address.City"
}

Use Case: Extracting specific data from complex nested objects.

4. Array First Element

Get the first item from an array:

{
  "Field": "PrimaryProductName",
  "DataExtractionPattern": "$.EnrichedData.Products[0].ProductName"
}

Use Case: When multiple related items exist but you only need the primary/first one.

5. Multiple Criteria Filter

Filter arrays using multiple conditions:

{
  "Field": "ActiveOrderNumber",
  "DataExtractionPattern": "$.EnrichedData.Orders[?(@.CustomerId == {CustomerId} && @.Status == 'Active')].OrderNumber"
}

Use Case: Complex lookups requiring multiple filter conditions.

6. Status Code Translation

Translate status codes to display names:

{
  "Field": "StatusCode",
  "DataExtractionPattern": "$.EnrichedData.Statuses[?(@.Code == {StatusCode})].DisplayName"
}

Variations:

// Numeric status codes
"$.EnrichedData.Statuses[?(@.StatusId == {StatusId})].StatusName"

// String-based enum mapping
"$.EnrichedData.StatusMappings[?(@.Key == '{Status}')].Value"

Use Case: Converting internal codes/enums to user-friendly labels.

7. User Information Lookup

Resolve user IDs to full names or email addresses:

{
  "Field": "CreatedBy",
  "DataExtractionPattern": "$.EnrichedData.Users[?(@.UserId == {CreatedBy})].FullName"
}

Variations:

// Email address
"$.EnrichedData.Users[?(@.UserId == {CreatedBy})].Email"

// Display name with department
"$.EnrichedData.Users[?(@.UserId == {CreatedBy})].DisplayName"

Use Case: Showing who performed actions in a human-readable format.

8. Category/Classification Mapping

Map category IDs to readable names:

{
  "Field": "CategoryId",
  "DataExtractionPattern": "$.EnrichedData.Categories[?(@.CategoryId == {CategoryId})].CategoryName"
}

Use Case: Translating hierarchical or classification data.

9. Locale-Specific Values

Extract localized display values:

{
  "Field": "ProductCode",
  "DataExtractionPattern": "$.EnrichedData.Translations[?(@.Code == {ProductCode})].Name.en"
}

Variations:

// Khmer locale
"$.EnrichedData.Translations[?(@.Code == {ProductCode})].Name.km"

// Dynamic locale (requires additional enricher logic)
"$.EnrichedData.Products[?(@.Code == {ProductCode})].LocalizedName"

Use Case: Multi-language support where different translations exist.

10. Parent Entity Reference

Access parent entity information:

{
  "Field": "ParentId",
  "DataExtractionPattern": "$.EnrichedData.ParentEntity.Name"
}

Use Case: Showing hierarchical relationships (parent-child entities).

11. Lookup Tables (Reference Data)

Map lookup values to display text:

{
  "Field": "CountryCode",
  "DataExtractionPattern": "$.EnrichedData.Countries[?(@.Code == '{CountryCode}')].Name"
}

Note: String values use single quotes in the filter expression.

Use Case: Reference data like countries, currencies, units of measure.

12. Boolean to Text Conversion

Convert boolean values to readable text:

{
  "Field": "IsActive",
  "DataExtractionPattern": "$.EnrichedData.BooleanMappings[?(@.Field == 'IsActive' && @.Value == {IsActive})].DisplayText"
}

Alternative: Use StaticEnrichedData for simple boolean mappings (recommended):

{
  "Field": "IsActive",
  "StaticEnrichedData": {
    "true": "Active",
    "false": "Inactive"
  }
}

Use Case: Converting true/false to "Yes"/"No", "Active"/"Inactive", etc.

Advanced Patterns

13. Accessing After/Before/Current Documents

Target specific audit log documents:

// Access the After document directly
"$.After.CustomerId"

// Access the Before document
"$.Before.OriginalStatus"

// Access the Current document
"$.Current.UpdatedValue"

Use Case: When you need values from specific audit log snapshots.

14. Combined Document Access with Enriched Lookup

Combine audit document values with enriched data:

{
  "Field": "CustomerId",
  "DataExtractionPattern": "$.EnrichedData.Customers[?(@.Id == $.After.CustomerId)].Name"
}

Note: This pattern doesn't use placeholder replacement but directly references another document path.

15. Array All Elements

Get all matching values (returns comma-separated):

{
  "Field": "OrderId",
  "DataExtractionPattern": "$.EnrichedData.Orders[?(@.OrderId == {OrderId})].Items[*].ProductName"
}

Result: "Product A, Product B, Product C"

Use Case: Showing multiple related items as a summary.

16. Conditional Value Selection

Select values based on conditions:

{
  "Field": "PaymentMethod",
  "DataExtractionPattern": "$.EnrichedData.PaymentMethods[?(@.Code == '{PaymentMethod}' && @.IsActive == true)].DisplayName"
}

Use Case: Filtering out inactive or deprecated reference data.

17. Array-to-Array Mapping (NEW)

Convert an array of codes/IDs to an array of display names:

{
  "Field": "License.Information.LicenseTypes",
  "DisplayName": "License Types",
  "DataExtractionPattern": "$.EnrichedData.licenseTypes[?(@.code == '{License.Information.LicenseTypes}')].name"
}

How it works:

• When the field value is a JSON array like ["LT02", "LT03"], the system automatically detects it
• For each element in the array, the placeholder {License.Information.LicenseTypes} is replaced with that element
• Pattern for first element: $.EnrichedData.licenseTypes[?(@.code == 'LT02')].name → "អាជ្ញាបណ្ណផលិត"
• Pattern for second element: $.EnrichedData.licenseTypes[?(@.code == 'LT03')].name → "អាជ្ញាបណ្ណចែកចាយ"
• Results are automatically joined with ", " separator
• Final output: "អាជ្ញាបណ្ណផលិត, អាជ្ញាបណ្ណចែកចាយ"

Example enriched data structure:

{
  "enrichedData": {
    "licenseTypes": [
      { "id": 36, "code": "LT02", "name": "អាជ្ញាបណ្ណផលិត" },
      { "id": 37, "code": "LT03", "name": "អាជ្ញាបណ្ណចែកចាយ" },
      { "id": 38, "code": "LT04", "name": "អាជ្ញាបណ្ណបញ្ជូនរង" }
    ]
  }
}

Common use cases:

• Multi-select fields (categories, tags, roles)
• Many-to-many relationships
• Permission sets
• Feature flags
• License types or certifications

Variations:

// Numeric ID arrays
{
  "Field": "CategoryIds",
  "DataExtractionPattern": "$.EnrichedData.Categories[?(@.Id == {CategoryIds})].Name"
}
// Input: [1, 5, 10]
// Output: "Electronics, Books, Clothing"

// User role arrays
{
  "Field": "RoleCodes",
  "DataExtractionPattern": "$.EnrichedData.Roles[?(@.Code == '{RoleCodes}')].DisplayName"
}
// Input: ["ADMIN", "EDITOR", "VIEWER"]
// Output: "Administrator, Content Editor, Viewer"

// Product SKU arrays
{
  "Field": "ProductSkus",
  "DataExtractionPattern": "$.EnrichedData.Products[?(@.Sku == '{ProductSkus}')].ProductName"
}
// Input: ["SKU001", "SKU045", "SKU099"]
// Output: "Widget A, Widget B, Widget C"

Fallback behavior:

• If extraction fails for any element, the original value is included in the result
• If the entire array cannot be processed, the formatted original array is returned
• Missing enriched data for specific codes still shows those codes in output

Use Case: Converting arrays of reference codes/IDs to human-readable names for multi-select fields, tags, categories, or many-to-many relationships.

Pattern Syntax Reference

Placeholder Replacement

JSONPath Operators

Filter Operators

Best Practices

1. Use Specific Paths

Prefer specific paths over recursive descent:

// Good
"$.EnrichedData.Customers[?(@.Id == {CustomerId})].Name"

// Avoid (slower)
"$..Name"

2. Consider StaticEnrichedData for Simple Mappings

For static key-value mappings, use StaticEnrichedData instead:

{
  "Field": "StatusCode",
  "StaticEnrichedData": {
    "1": "Active",
    "2": "Inactive",
    "3": "Pending"
  }
}

Advantages:

• Faster (no JSONPath parsing)
• No enricher needed
• Easier to maintain
• Supports locale nesting

3. Test Patterns with Sample Data

Always validate patterns with sample enriched data:

{
  "EnrichedData": {
    "Customers": [
      { "Id": 123, "Name": "John Doe" },
      { "Id": 456, "Name": "Jane Smith" }
    ]
  }
}

Test pattern: $.EnrichedData.Customers[?(@.Id == 123)].Name
Expected result: "John Doe"

4. Handle Missing Data Gracefully

Patterns return null if no match is found, falling back to original value automatically.

5. Use Appropriate Enrichers

Ensure the required data is available in EnrichedData by implementing the necessary enrichers:

• IAuditEventEnricher implementations
• Custom enrichers for specific lookup tables
• Batch enrichers for performance

Common Patterns by Entity Type

Customer Fields

{
  "Field": "CustomerId",
  "DataExtractionPattern": "$.EnrichedData.Customers[?(@.Id == {CustomerId})].Name"
}

Product Fields

{
  "Field": "ProductId",
  "DataExtractionPattern": "$.EnrichedData.Products[?(@.Id == {ProductId})].ProductName"
}

Order Fields

{
  "Field": "OrderId",
  "DataExtractionPattern": "$.EnrichedData.Orders[?(@.Id == {OrderId})].OrderNumber"
}

User Fields

{
  "Field": "UserId",
  "DataExtractionPattern": "$.EnrichedData.Users[?(@.Id == {UserId})].FullName"
}

Status Fields

{
  "Field": "StatusId",
  "DataExtractionPattern": "$.EnrichedData.Statuses[?(@.Id == {StatusId})].StatusName"
}

Troubleshooting

Pattern Not Working?

1. Check enriched data structure: Verify the data exists at the specified path
2. Case sensitivity: The system handles case-insensitive matching, but verify property names
3. Placeholder syntax: Ensure {FieldName} matches the actual field name exactly
4. Filter expressions: Validate filter syntax and value types (numeric vs string)
5. Check logs: Look for warnings in application logs for extraction failures

Common Errors

Related Documentation

• DataExtractionService.cs - Implementation details
• ExtractValueFromJsonPath - Technical pattern documentation
• EntityFieldDisplayConfig - Field configuration schema
• IAuditEventEnricher - Enricher implementation guide

Examples by Use Case

E-commerce System

{
  "Table": "Orders",
  "Configurations": [
    {
      "Field": "CustomerId",
      "DisplayName": "Customer",
      "DataExtractionPattern": "$.EnrichedData.Customers[?(@.Id == {CustomerId})].Name"
    },
    {
      "Field": "ProductId",
      "DisplayName": "Product",
      "DataExtractionPattern": "$.EnrichedData.Products[?(@.Id == {ProductId})].ProductName"
    },
    {
      "Field": "StatusId",
      "DisplayName": "Order Status",
      "DataExtractionPattern": "$.EnrichedData.Statuses[?(@.Id == {StatusId})].DisplayName"
    }
  ]
}

HR/Employee System

{
  "Table": "Employees",
  "Configurations": [
    {
      "Field": "DepartmentId",
      "DisplayName": "Department",
      "DataExtractionPattern": "$.EnrichedData.Departments[?(@.Id == {DepartmentId})].DepartmentName"
    },
    {
      "Field": "ManagerId",
      "DisplayName": "Manager",
      "DataExtractionPattern": "$.EnrichedData.Employees[?(@.Id == {ManagerId})].FullName"
    },
    {
      "Field": "PositionCode",
      "DisplayName": "Position",
      "DataExtractionPattern": "$.EnrichedData.Positions[?(@.Code == '{PositionCode}')].Title"
    }
  ]
}

Inventory System

{
  "Table": "StockMovements",
  "Configurations": [
    {
      "Field": "ProductId",
      "DisplayName": "Product",
      "DataExtractionPattern": "$.EnrichedData.Products[?(@.Id == {ProductId})].ProductName"
    },
    {
      "Field": "WarehouseId",
      "DisplayName": "Warehouse",
      "DataExtractionPattern": "$.EnrichedData.Warehouses[?(@.Id == {WarehouseId})].Name"
    },
    {
      "Field": "MovementTypeId",
      "DisplayName": "Movement Type",
      "StaticEnrichedData": {
        "1": "Receipt",
        "2": "Shipment",
        "3": "Transfer",
        "4": "Adjustment"
      }
    }
  ]
}

Performance Considerations

1. Pattern Complexity: Simple property access is faster than complex filter expressions
2. Enriched Data Size: Larger enriched documents take longer to parse
3. Array Filtering: Filtering large arrays can impact performance
4. Caching: Field configurations are cached (default 5 minutes)
5. Batch Operations: Multiple lookups are processed efficiently in batch transforms

Version History

• v1.0: Initial pattern documentation
• v1.1: Added StaticEnrichedData examples and best practices
• v1.2: Added troubleshooting section and use case examples