Question 110186 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

Broke nested dynamic JSON array with JOLT

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.

Tags:

2

Answers


  1. 0

    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"
        }
      }
    }
  }
]

    enter image description here

    Login or Signup to reply.
  2. 0

    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

    enter image description here

    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"
        }
      }
    }
  }
]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search