Broke nested dynamic JSON array with JOLT

Question

I'm looking for flattening nested JSON file into SQL ready format.

JSON file's content:

{
  "ProductLine": [
    "Product 1",
    "Product 2"
  ],
  "Purchase": 364,
  "Cancel": [
    140,
    2
  ]
}

My current transformation:

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[#2].&2"
        }
      }
    }
  }
]

Desired output:

[
  {
    "ProductLine": "Product 1",
    "Purchase": 364,
    "Cancel": 140
  },
  {
    "ProductLine": "Product 2",
    "Cancel": 2
  }
]

The difficulty is that arrays can change, sometimes "Cancel" can be an array or sometimes "Purchase" block can be nested.

Mohammadreza Khedri · Accepted Answer · 2023-01-19 09:14:56Z

1

You can use this spec:

If Purchase or cancel be an array or not, this works

[
  {
    "operation": "cardinality",
    "spec": {
      "*": "MANY"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "ProductLine": {
        "*": {
          "*": {
            "@1": "[&2].&3",
            "@(3,Purchase[&1])": "[&2].Purchase",
            "@(3,Cancel[&1])": "[&2].Cancel"
          }
        }
      }
    }
  }
]

First, change all values to the array. Now you can loop on the ProductLine and get other fields from Purchase and Cancel.

Update: The following answer has been obtained in collaboration with Barbaros Özhan. Special thanks.

[
  {
    "operation": "cardinality",
    "spec": {
      "*": "MANY"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[#2].&2"
        }
      }
    }
  }
]

edited Jan 19, 2023 at 9:14

answered Jan 18, 2023 at 10:46

Mohammadreza Khedri

2,7392 gold badges13 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Barbaros Özhan Over a year ago

Now I'm able to examine yours. Using cardinality for this case is such a pretty good idea, great!

Mohammadreza Khedri Over a year ago

@BarbarosÖzhan Thank you. I learned all this from you.

Barbaros Özhan · Accepted Answer · 2023-01-19 08:13:15Z

1

We can pick Purchase at a different(outer) level such as

[
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[#2].&2" 
        }
      },
      "Purchase": "[#].&"// at two level less than the inner object
    }
  }
]

the demo one the site http://jolt-demo.appspot.com/ is

Edit : Considering array indeterminance for the attributes, you can use the following spec alternatively

[
  { //reform two separate objects
    "operation": "shift",
    "spec": {
      "@": "orj",
      "*": "non_array.&.@0[]"
    }
  },
  { // in order to keep the non-array values as the first component of the newly formed array(s) 
    "operation": "sort"
  },
  {
    "operation": "shift",
    "spec": {
      "*": { //the topmost level 
        "*": { //level for the keys
          "*": "&1[]" //match keys and values to convert non-arrays to arrays
        }
      }
    }
  },
  {// pick the first component for the non-array(s)
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "*": "=firstElement"
      }
    }
  },
  { // apply the original spec after having got individual array values
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[#2].&2"
        }
      }
    }
  },
  { //get rid of the attributes with null values
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": "=recursivelySquashNulls"
    }
  }
]

or another straightforward alternative would be using your original spec after applying cardinality spec such as

[
  {
    "operation": "cardinality",
    "spec": {
      "*": "MANY"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[#2].&2"
        }
      }
    }
  }
]

edited Jan 19, 2023 at 8:13

answered Jan 18, 2023 at 10:49

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

6 Comments

Mohammadreza Khedri Over a year ago

Its rights according to the exact OP. But at the end of the question it is mentioned that cancel can be an array. In this case I think that cardinality is force to use for first spec.

Barbaros Özhan Over a year ago

Hi @MohammadReza , I was busier yesterday, can you check this new update out as well please.

Mohammadreza Khedri Over a year ago

Using [#2] in the last solution is a better solution.

Mohammadreza Khedri Over a year ago

For the Considering array indeterminance solution I think it's another solution if we had not the cardinality operation in the jolt. It is an interesting solution.

Mohammadreza Khedri Over a year ago

I respectfully added your last solution to my answer. +1

|

Collectives™ on Stack Overflow

Broke nested dynamic JSON array with JOLT

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related