JSON data extract in preprosess

OL Connect Professional DataMapper
1

HI All ,

I have a JSON datafile , but I need to extract the total amount of Boxes in the data . I can get the total per customer , but not total for all . I need to use this the get the same amount of token in a SQL query in the next step ( completed) .

Should it be done in a Preprocess ? similar to the CSV delimiter script on the OL site . I’ve tried using JSON path in various ways without luck .

‘$…boxid’ / data.extract(‘$.invoice_list[*].picklist.boxes’);

image
image595×651 19.9 KB

DAT2405100001__23-05-2024_11-08-5454-rep-mod.zip (93.4 KB)

I’ll send the datamapper if somone can assist .

Regards
Mark

2

What I need is the sum of the results on the right for the SQL query

image
image1315×674 50.8 KB

3

You would best do this in an extract field step and set the field definition to be based on JavaScript:

image
image1103×568 17.7 KB

var data = JSON.parse(data.extract('$'));
var sum = 0;

data.invoice_list.forEach(function(invoice) {
    if (invoice.picklist && typeof invoice.picklist.boxes === 'number') {
        sum += invoice.picklist.boxes;
    }
});

sum;
4

Thanks Jim

I only seem to get the total boxes per invoice and not the total overall .
Almost like its per invoice and the counter gets reset .

image
image1367×951 69.6 KB

5

Yes, I see - it’s because in my example, I have the document boundary set to root. We will need a preprocessor in that case which adds the total to the original data.

Here is an example:

/* Calculate number of boxes at various points
   throughout JSON file
-  J. Stacey (Point Zero Solutions)
-  2024-06-18
*/
	
// Create a temporary file
var tmpFile = createTmpFile();

// Open the original file for reading
var originalFile = openTextReader(data.filename);

// Open the temporary file for writing
var modifiedFile = openTextWriter(tmpFile.getPath());

// Read the JSON content from the original file
var jsonContent = "";
var line;
while ((line = originalFile.readLine()) !== null) {
    jsonContent += line;
}

// Parse the JSON content
var jsonData = JSON.parse(jsonContent);

// Calculate the sum of the 'boxes' values
var sum = 0;
jsonData.invoice_list.forEach(function(invoice) {
    if (invoice.picklist && typeof invoice.picklist.boxes === 'number') {
        sum += invoice.picklist.boxes;
    }
});

// Add the total sum to the JSON object
jsonData.total_boxes = sum;

// Convert the JSON object back to a string
var modifiedJsonContent = JSON.stringify(jsonData, null, 2); // Pretty-print with 2 spaces

// Write the modified JSON content to the temporary file
modifiedFile.write(modifiedJsonContent);

// Close the files
modifiedFile.close();
originalFile.close();

// Replace the original file with the modified file
deleteFile(data.filename);
tmpFile.move(data.filename);

// Print the result to the console
logger.info("Total boxes: " + sum);

image

6

Thanks for this Jim . I will only test it tomorrow .

I’ve done a CSV preprocess before but this is very useful . I may even try use this to merge my second datafile at runtime.

If you have any other useful scripts I would love to play around and learn from them .

Thanks Again

7

HI Jim

Just wanted to say it works flawlessly, thanks ! . I managed to get some SQL data into the JSON data so it was extra beneficial.

Regards
Mark

1 Like