使用JOLT中断嵌套的动态JSON数组

ibps3vxo  于 2023-01-22  发布在  其他
关注(0)|答案(2)|浏览(159)

我正在寻找扁平嵌套JSON文件到SQL就绪格式。
JSON文件的内容:

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

我目前的转型:

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

预期输出:

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

困难在于数组可以改变,有时"Cancel"可以是一个数组,有时"Purchase"块可以嵌套。

eit6fx6z

eit6fx6z1#

可以使用此等级库:

    • 无论Purchasecancel是否为数组,此操作都有效**
[
  {
    "operation": "cardinality",
    "spec": {
      "*": "MANY"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "ProductLine": {
        "*": {
          "*": {
            "@1": "[&2].&3",
            "@(3,Purchase[&1])": "[&2].Purchase",
            "@(3,Cancel[&1])": "[&2].Cancel"
          }
        }
      }
    }
  }
]

首先,将数组中的所有值更改为数组,然后可以在ProductLine上循环,并从PurchaseCancel中获取其他字段。

    • 更新:**以下答案是与Barbaros Özhan合作获得的。特别感谢。
[
  {
    "operation": "cardinality",
    "spec": {
      "*": "MANY"
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": {
          "@": "[#2].&2"
        }
      }
    }
  }
]

inb24sb2

inb24sb22#

我们可以在不同的(outer)级别选择Purchase,例如

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

站点http://jolt-demo.appspot.com/的*演示

编辑:考虑到属性的数组不确定性,您也可以使用以下规范

[
  { //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"
    }
  }
]

或者另一个直接的选择是在应用基数规范之后使用原始规范,例如

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

相关问题