Our requirement is to process JSON data files, validate the records and write the output to tables in a MySQL database.
Once each record is validated:
New records are to be inserted into the table
Existing records are to be updated.
An audit table will be updated at the same time:
For a new record, each field changed will be added to the audit table with "New Record"
For updates each field will be added with field changed, current value and updated value.
Process each file and validate all records and insert records into the table or update existing records.
The file layout is below, there is a meta record and then product records, the files range in size from 60mb to 355mb
{
"meta": {
"disclaimer": "Copyright © 2022 GS1 AISBL. This data is intended only for the
designated user company under the terms of the agreement with the local GS1
Member Organisation. Unauthorised use, duplication, or sharing is prohibited."
},
"data":[
{
"gtin":"08908008772252",
"gpcCategoryCode":"10000284",
"licenseeName":"Nebico Pvt Ltd",
"brandName":[{"language":"en-IN","value":"NEBICO OATMEAL"}],
"productDescription":[{"language":"en-IN","value":"NEBICO OATMEAL 1 Kg 1000 Grams"}],
"productImageUrl":[{
"language":"en-IN",
"value":"https:\/\/[login to view URL]\/upload\/image\/[login to view URL]"
}],
"countryOfSaleCode":[{
"numeric":"356",
"alpha2":"IN",
"alpha3":"IND"
}],
"netContent":[{
"unitCode":"GRM",
"value":"1000.0"
}],
"dateCreated":"2020-11-05T11:03:53",
"dateUpdated":"2020-11-07T09:15:17"
},
{
"gtin":"08908008772023",
"gpcCategoryCode":"10000160",
"licenseeName":"Nebico Pvt Ltd",
"brandName":[{
"language":"en-IN",
"value":"SUGAR FREE CRACKER"}],
"productDescription":[{
"language":"en-IN",
"value":"Sugar Free Cracker Nebico Sugarfree 250 Grams Premium Cracker."}],
"productImageUrl":[{
"language":"en-IN",
"value":"https:\/\/[login to view URL]\/upload\/image\/[login to view URL]"}],
"countryOfSaleCode":[{
"numeric":"356",
"alpha2":"IN",
"alpha3":"IND"}],
"netContent":[{
"unitCode":"GRM",
"value":"250.0"}],
"dateCreated":"2020-02-20T06:25:24","
"dateUpdated":"2020-02-21T08:10:11"},
{
"gtin":"08908008772313",
"gpcCategoryCode":"10000160",
"licenseeName":"Nebico Pvt Ltd",
"brandName":[{
"language":"en-IN",
"value":"MULTRIGRAIN TREKKERS CHOICE"}],
"productDescription":[{
"language":"en-IN",
"value":"MULTRIGRAIN TREKKERS CHOICE 100 Milligrams"}],
"productImageUrl":[{
"language":"en-IN",
"value":"https:\/\/[login to view URL]\/image\/89072\/[login to view URL]"}],
"countryOfSaleCode":[{
"numeric":"356",
"alpha2":"IN",
"alpha3":"IND"}],
"netContent":[{
"unitCode":"MGM",
"value":"100"}],
"dateCreated":"2021-06-24T07:30:47",
"dateUpdated":"2021-09-15T08:46:47"}]
}
As part of this project, we need to process files regularly, a process for adding files to a queue, writing the results to a table and emailing a summary as part of workflow will be needed. This can be discussed to develop the full scope and cost of the project.