Audit key-value pairs

The Compare/KeyValuePairAudit extension compares key-value state code records from two JSON string inputs and identifies records that do not have a matching code and description pair in the other dataset.

This is useful when auditing reference data from an Excel source against runtime data, especially when you need to confirm that state codes and state descriptions are aligned across both systems.

Parameters:

• excelData required, a JSON string representing an array of objects from the Excel/reference source. Each object is expected to include string fields named key and value;
• runTimeData required, a JSON string representing an array of runtime objects. Each object is expected to include string fields named STATE-CODE-S and STATE-CODE-DESC.

NLP usage

Use the extension in a journey by calling Compare/KeyValuePairAudit with the execute command. Pass each value to the matching extension input using as inputName.

execute "Compare/KeyValuePairAudit" using '[{"id":"68e6c0bc-3b4b-4f3e-b71b-c14bc7feb803","path":"Claim:StateCodeLloyds","key":"AB","value":"ALBERTA","effectiveDate":"2023-07-13","deprecatedDate":"9999-12-31"}]' as excelData and '[{"REGION":"CAN","STATE-CODE-DESC":"ALBERTA                       ","STATE-CODE-S":"AB"},{"REGION":"USA","STATE-CODE-DESC":"ALASKA                        ","STATE-CODE-S":"AK"}]' as runTimeData returning $response

execute "Compare/KeyValuePairAudit" using '[{"key":"CO","value":"COLORADO"}]' as excelData and '[{"STATE-CODE-S":"CO","STATE-CODE-DESC":"COLORADO"}]' as runTimeData returning $response

You can also pass JSON strings stored in variables when the source data is prepared earlier in the journey.

execute "Compare/KeyValuePairAudit" using "$excelData" as excelData and "$runTimeData" as runTimeData returning $response

store value '[{"id":"68e6c0bc-3b4b-4f3e-b71b-c14bc7feb803","path":"Claim:StateCodeLloyds","key":"AB","value":"ALBERTA","effectiveDate":"2023-07-13","deprecatedDate":"9999-12-31"}]' in $excelData
store value '[{"REGION":"CAN","STATE-CODE-DESC":"ALBERTA                       ","STATE-CODE-S":"AB"},{"REGION":"USA","STATE-CODE-DESC":"ALASKA                        ","STATE-CODE-S":"AK"}]' in $runTimeData
execute "Compare/KeyValuePairAudit" using "$excelData" as excelData and "$runTimeData" as runTimeData returning $response

Example output when a runtime record is not found in the Excel/reference dataset:

{
  "jsonMismatchedPairs": [],
  "inputMismatchedPairs": [
    {
      "REGION": "USA",
      "STATE-CODE-DESC": "ALASKA                        ",
      "STATE-CODE-S": "AK"
    }
  ]
}

This extension does not require any external resource.

The extension should be configured as:

• Run asynchronously: No
• Scope: Global

Add the extension to your Virtuoso instance

Select the domain that matches your Virtuoso account.

/*const json = [
  {
    "id": "68e6c0bc-3b4b-4f3e-b71b-c14bc7feb803",
    "path": "Claim:StateCodeLloyds",
    "key": "AB",
    "value": "ALBERTA",
    "effectiveDate": "2023-07-13",
    "deprecatedDate": "9999-12-31"
  },
  {
    "id": "8aa367c9-e111-435e-998c-35b0e86c0ce3",
    "path": "Claim:StateCodeLloyds",
    "key": "CO",
    "value": "COLORADO",
    "effectiveDate": "2023-07-13",
    "deprecatedDate": "9999-12-31"
  }
];

const input = [
  {
    "REGION": "CAN",
    "STATE-CODE-DESC": "ALBERTA                       ",
    "STATE-CODE-S": "AB"
  },
  {
    "REGION": "USA",
    "STATE-CODE-DESC": "ALASKA                        ",
    "STATE-CODE-S": "AK"
  }
];*/
const json = JSON.parse(excelData);
const input = JSON.parse(runTimeData);

let jsonMismatchedPairs = [];
let inputMismatchedPairs = [];
let jsonMatchFound = false;
let inputMatchFound = false;

for (const jsonItem of json) {
  let isMatched = false;
  for (const inputItem of input) {
    const jsonKey = jsonItem.key.trim().toLowerCase();
    const inputValue = jsonItem.value.trim().toLowerCase();
    const inputKey = inputItem["STATE-CODE-S"].trim().toLowerCase();
    const inputDesc = inputItem["STATE-CODE-DESC"].trim().toLowerCase();

    if (
      typeof jsonItem.key === 'string' && typeof inputItem["STATE-CODE-S"] === 'string' &&
      typeof jsonItem.value === 'string' && typeof inputItem["STATE-CODE-DESC"] === 'string' &&
      jsonKey === inputKey && inputValue === inputDesc
    ) {
      isMatched = true;
      break;
    }
  }

  if (!isMatched) {
    jsonMismatchedPairs.push(jsonItem);
    jsonMatchFound = true;
  }
}

for (const inputItem of input) {
  let isMatched = false;
  for (const jsonItem of json) {
    const jsonKey = jsonItem.key.trim().toLowerCase();
    const inputValue = jsonItem.value.trim().toLowerCase();
    const inputKey = inputItem["STATE-CODE-S"].trim().toLowerCase();
    const inputDesc = inputItem["STATE-CODE-DESC"].trim().toLowerCase();

    if (
      typeof inputItem["STATE-CODE-S"] === 'string' && typeof jsonItem.key === 'string' &&
      typeof inputItem["STATE-CODE-DESC"] === 'string' && typeof jsonItem.value === 'string' &&
      inputKey === jsonKey && inputDesc === inputValue
    ) {
      isMatched = true;
      break;
    }
  }

  if (!isMatched) {
    inputMismatchedPairs.push(inputItem);
    inputMatchFound = true;
  }
}

if (!jsonMatchFound && !inputMatchFound) {
  return "No mismatches found.";
}

console.log("Mismatched pairs in 'json':");
console.log(jsonMismatchedPairs);

console.log("Mismatched pairs in 'input':");
console.log(inputMismatchedPairs);

return {
  jsonMismatchedPairs,
  inputMismatchedPairs
};