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.

CodePudding user response：

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.

CodePudding user response：

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