How do i transform a nested json to Jolt in Apache NIFI

Question

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

I would recommend checking out https://jolt-demo.appspot.com/ which is great place for a learning how to do this and try different JOLT values against your data. — Mike R, Sep 15 '22 at 13:23

Barbaros Özhan · Accepted Answer · 2022-09-15T22:30:12.657

2

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": {
      "*": {
        "*": {
          "*": ""
        }
      }
    }
  }
]

edited Sep 15 '22 at 22:30

answered Sep 15 '22 at 14:03

Barbaros Özhan

59,113
10
31
55

Thnx. I tried your solution in my real data. The result returns arrays for each tag. [{ "model": ["A01 Core", "A12 Nacho", "A13",], "total_count": [0, 0, 17, 2, 5, 0, 10], "items_model": ["A01 Core", "A12 Nacho", "A13", "A22",], "systemCode": ["2000010010001001001001001", "2000010010006002001005002", "2000010010007001001001001", "2000010010009006001001002"] , ... . tskrl – bibiji Sep 15 '22 at 14:46
1

The desired format is exactly what you got as a result. – bibiji Sep 15 '22 at 14:50
I'm checking it. My be it's my fault. – bibiji Sep 15 '22 at 14:59
I updated my question. My input was in incorrect format. – bibiji Sep 15 '22 at 15:26
@bibiji you can add **`.[&3]`** node which represents the indexes of **models** object in order to partition the result finer. I've edited the answer by reflecting this issue. – Barbaros Özhan Sep 15 '22 at 17:48
1

Thanks @Barbaros Özhan. How do i learn JOLST transformation? Is there any resources to learn this skill? – bibiji Sep 16 '22 at 08:36
1

Hi @bibiji , there are some blogs and sites I like such as [1](https://github.com/bazaarvoice/jolt/releases), [2](https://erbalvindersingh.medium.com/applying-jolttransform-on-json-object-array-and-fetching-specific-fields-48946870b4fc), [3](https://intercom.help/godigibee/en/articles/3096940-simple-if-else-with-jolt), [4](https://community.cloudera.com/t5/Community-Articles/Jolt-quick-reference-for-Nifi-Jolt-Processors/ta-p/244350), [5](https://cool-cheng.blogspot.com/2019/12/json-jolt-tutorial.html) – Barbaros Özhan Sep 16 '22 at 08:59

How do i transform a nested json to Jolt in Apache NIFI

1 Answers1