Cloudera Community
Announcements
Celebrating as our community reaches 100,000 members! Thank you!
wetland
user rank
New Contributor

How do i transform a nested json to Jolt in NIFI

by New Contributor in Support Questions
‎09-15-2022 03:51 AM
‎09-15-2022 03:51 AM
I have a json format like the below JSON: { "massage": 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?    Thank you ... View more
Labels:

Re: How do i set parameters of rest api in InvokeH...

by Guru in Support Questions
‎09-09-2022 11:01 AM
1 Kudo
‎09-09-2022 11:01 AM
1 Kudo
Hi, Not sure how you get the URL but if its hard coded in the InvokeHttp Remote URL property , then you can use expression language to set up your parameters as follows:   ${literal('https://api.aa/reports/api/order_report/?format=json&dateFrom=#DATEFROM&dateTo=#DATETO'): replace('#DATEFROM',${now():format('yyyy-MM-dd')}): replace('#DATETO',${now():toNumber():minus(432000000):format("yyyy-MM-dd")})}   Notice: - you can set the date format as desired, in my case I assumed that its in yyyy-MM-dd format. - To subtract 5 days from the current date you have to convert the date to number , then subtract 5 days in milliseconds (1day = 86,400,000 mls ). Hope that helps, if it does please accept solution. Thanks ... View more
Contact Me
Online
Offline
Last Visited
‎10-25-2022 08:33 AM
Community Statistics
Member Since ‎09-09-2022 03:33 AM
Last Visited ‎10-25-2022 08:33 AM
Posts 2