I have an API with the below JSON format:
{
"message": true,
"result": [
{
"category": "Device",
"total_count": 288,
"total_price": 1769005231,
"brands": [
{
"brand": "DD",
"total_count": 127,
"total_price": 815344231,
"models": [
{
"model": "A01 Core",
"total_count": 0,
"total_price": 31231,
"items": [
{
"systemCode": "2000010010001001001001001",
"sku": "TEST DD A01 Core (1GB 16GB 4G) VV",
"model": "A01 Core",
"seller": "DSA",
"color": "Black",
"guaranty": "AORD",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 31231
},
{
"systemCode": "2000010010001001001001001",
"sku": "TEST DD A01 Core (1GB 16GB 4G) VV",
"model": "A01 Core",
"seller": "DSA",
"color": "Black",
"guaranty": "AORD",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 1000000
}
]
},
{
"model": "A13",
"total_count": 12,
"total_price": 61237000,
"items": [
{
"systemCode": "2000010010007001001001002",
"sku": "TEST DD A13 (4GB 64GB 4G) VV",
"model": "A13",
"seller": "DSA",
"color": "Black",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 4249000
}
]
},
{
"model": "A22 5G",
"total_count": 0,
"total_price": 5239000,
"items": [
{
"systemCode": "2000010010010004001016002",
"sku": "TEST DD A22 5G (4GB 128GB 5G) II",
"model": "A22 5G",
"seller": "DSA",
"color": "White",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 5147010,
"price": 5239000
},
{
"systemCode": "2000010010010007001003002",
"sku": "TEST DD A22 5G (4GB 128GB 5G) RX | DSA | SSS [Gray]",
"model": "A22 5G",
"seller": "DSA",
"color": "Gray",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 5398044,
"price": 5299000
},
{
"systemCode": "2000010010010007001016002",
"sku": "TEST DD A22 5G (4GB 128GB 5G) RX | DSA | SSS [White]",
"model": "A22 5G",
"seller": "DSA",
"color": "White",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 5239000
}
]
},
{
"model": "A52",
"total_count": 0,
"total_price": 8299000,
"items": [
{
"systemCode": "2000010010014002001001002",
"sku": "TEST DD A52 (8GB 128GB 4G) II",
"model": "A52",
"seller": "DSA",
"color": "Black",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 8299000
},
{
"systemCode": "2000010010014004001005002",
"sku": "TEST DD A52 (8GB 256GB 4G) II",
"model": "A52",
"seller": "DSA",
"color": "Blue",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 9099000
}
]
},
{
"model": "A52s",
"total_count": 12,
"total_price": 152487000,
"items": [
{
"systemCode": "2000010010015002001008001",
"sku": "TEST DD A52s (8GB 128GB 5G) II",
"model": "A52s",
"seller": "DSA",
"color": "Mint",
"guaranty": "AORD",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 9701010,
"price": 10159000
},
{
"systemCode": "2000010010015004001001001",
"sku": "TEST DD A52s (8GB 256GB 5G) II",
"model": "A52s",
"seller": "DSA",
"color": "Black",
"guaranty": "AORD",
"stockId": "903",
"quantity": 7,
"reserve": 0,
"salable": 7,
"dailySales": 0,
"balancedAvg": 11681010,
"price": 11919000
},
{
"systemCode": "2000010010015004001016001",
"sku": "TEST DD A52s (8GB 256GB 5G) II",
"model": "A52s",
"seller": "DSA",
"color": "White",
"guaranty": "AORD",
"stockId": "903",
"quantity": 5,
"reserve": 0,
"salable": 5,
"dailySales": 0,
"balancedAvg": 11483010,
"price": 11779000
},
{
"systemCode": "2000010010015004001016002",
"sku": "TEST DD A52s (8GB 256GB 5G) II",
"model": "A52s",
"seller": "DSA",
"color": "White",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 11599000
}
]
},
{
"model": "A53",
"total_count": 0,
"total_price": 11379000,
"items": [
{
"systemCode": "2000010010016004001001002",
"sku": "TEST DD A53 (8GB 256GB 5G) II",
"model": "A53",
"seller": "DSA",
"color": "Black",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 11797000,
"price": 11379000
},
{
"systemCode": "2000010010016004001005002",
"sku": "TEST DD A53 (8GB 256GB 5G) II",
"model": "A53",
"seller": "DSA",
"color": "Blue",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 11855425,
"price": 11349000
}
]
},
{
"model": "A03",
"total_count": 0,
"total_price": 3099000,
"items": [
{
"systemCode": "2000010010003007001001001",
"sku": "TEST DD A03 (3GB 32GB 4G) RX",
"model": "A03",
"seller": "DSA",
"color": "Black",
"guaranty": "AORD",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 3099000
}
]
}
]
},
{
"brand": "red",
"total_count": 28,
"total_price": 230893000,
"models": [
{
"model": "Ride 3 Pro 5G",
"total_count": 0,
"total_price": 5189000,
"items": [
{
"systemCode": "2000010050005003001030002",
"sku": "TEST Ride 3 Pro 5G (6GB 128GB 5G) Global",
"model": "Ride 3 Pro 5G",
"seller": "DSA",
"color": "Power Black",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 5088000,
"price": 5189000
}
]
},
{
"model": "Ride 4 Pro ",
"total_count": 0,
"total_price": 6769000,
"items": [
{
"systemCode": "2000010050006002001030002",
"sku": "TEST Ride 4 Pro (8GB 256GB 4G) Global",
"model": "Ride 4 Pro ",
"seller": "DSA",
"color": "Power Black",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 6769000
},
{
"systemCode": "2000010050006002001032002",
"sku": "TEST Ride 4 Pro (8GB 256GB 4G) Global",
"model": "Ride 4 Pro ",
"seller": "DSA",
"color": "Cool Blue",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 6759000
}
]
},
{
"model": "Ride 4 Pro 5G",
"total_count": 0,
"total_price": 5759000,
"items": [
{
"systemCode": "2000010050007001001030002",
"sku": "TEST Ride 4 Pro 5G (6GB 128GB 5G) Global",
"model": "Ride 4 Pro 5G",
"seller": "DSA",
"color": "Power Black",
"guaranty": "SSS",
"stockId": "903",
"quantity": 0,
"reserve": 0,
"salable": 0,
"dailySales": 0,
"balancedAvg": 0,
"price": 5759000
}
]
},
{
"model": "red F3",
"total_count": 2,
"total_price": 10875000,
"items": [
{
"systemCode": "2000010050010002001102002",
"sku": "TEST red F3 (8GB 256GB 5G) Global | DSA | SSS [Arctic White]",
"model": "red F3",
"seller": "DSA",
"color": "Arctic White",
"guaranty": "SSS",
"stockId": "903",
"quantity": 2,
"reserve": 0,
"salable": 2,
"dailySales": 0,
"balancedAvg": 10450000,
"price": 10875000
}
]
},
{
"model": "red C31",
"total_count": 1,
"total_price": 3359000,
"items": [
{
"systemCode": "2000010050001004001043002",
"sku": "TEST red C31 (4GB 64GB 4G) II | DSA | SSS [Shadow Gray]",
"model": "red C31",
"seller": "DSA",
"color": "Shadow Gray",
"guaranty": "SSS",
"stockId": "903",
"quantity": 1,
"reserve": 0,
"salable": 1,
"dailySales": 0,
"balancedAvg": 3299000,
"price": 3359000
}
]
}
]
}
]
}
]
}
I want to transform this JSON to JOLT and import the result to a Mysql Table. The table has the below columns:
Brand ,Model ,total_count_model ,systemcode ,sku ,seller
How can i convert the JSON file to a Jolttransformjson?
I also use Jolt Transformation DSL and tried some JOLT specification.
Thank you
CodePudding user response：
You can walk through indexes of the innermost array(namely items) within a shift transformation spec such as
[
{
"operation": "shift",
"spec": {
"*": {
"*": {
"brands": {
"*": {
"models": {
"*": {
"items": {
"*": {
"@(4,brand)": "[&5].[&3].&1.brand",
"@(2,model)": "[&5].[&3].&1.model",
"@(2,total_count)": "[&5].[&3].&1.total_count",
"model": "[&5].[&3].&1.items_model",
"systemCode": "[&5].[&3].&1.systemCode",
"sku": "[&5].[&3].&1.sku",
"seller": "[&5].[&3].&1.seller"
}
}
}
}
}
}
}
}
}
},
{
// get rid of the object labels
"operation": "shift",
"spec": {
"*": {
"*": {
"*": ""
}
}
}
}
]