Search code examples
jsonflattenjolt

How to flatten a nested json

I want to flatten a nested input JSON. The JSON contains a product with "n" product attributes. Attached to the product are "n" items, each of this item can have "n" item attributes. My goal is to bring all the information to the root level. I'm able to do this for the top levels, but not for the nested arrays. Perhaps I need to do this in multiple steps, but I'm not sure how. This is my input Json:

{
  "product": {
    "attributes": [
      {
        "values": [
          {
            "unit_of_measure": null,
            "language": null,
            "value": "changable"
          },
          {
            "unit_of_measure": null,
            "language": null,
            "value": "washable"
          }
        ],
        "id": "cleaning",
        "kind": "option"
      },
      {
        "values": [
          {
            "unit_of_measure": null,
            "language": null,
            "value": "myTest"
          }
        ],
        "id": "how_to_purchase",
        "kind": "String"
      }
    ],
    "title": "myTitle",
    "category": {
      "id": "47114712"
    },
    "items": [
      {
        "item_number": "4711",
        "attributes": [
          {
            "values": [
              {
                "unit_of_measure": null,
                "language": null,
                "value": "2024-01-25T09:11:49"
              }
            ],
            "id": "timestamp",
            "kind": "String"
          },
          {
            "values": [
              {
                "unit_of_measure": "kg",
                "language": null,
                "value": 0.47
              }
            ],
            "id": "weight",
            "kind": "float"
          },
          {
            "values": [
              {
                "unit_of_measure": null,
                "language": null,
                "value": "orange"
              },
              {
                "unit_of_measure": null,
                "language": null,
                "value": "black"
              }
            ],
            "id": "colour",
            "kind": "option"
          }
        ]
      },
      {
        "item_number": "4712",
        "attributes": [
          {
            "values": [
              {
                "unit_of_measure": null,
                "language": null,
                "value": "2024-01-25T09:11:49"
              }
            ],
            "id": "timestamp",
            "kind": "String"
          },
          {
            "values": [
              {
                "unit_of_measure": null,
                "language": null,
                "value": true
              }
            ],
            "id": "valid",
            "kind": "boolean"
          }
        ]
      }
    ],
    "product_number": "myProduct123"
  }
}

This is my desired, flattened output (taking into acount, that an attribute can have several values)

{
    "p-myProduct123-cleaning-0-unit_of_measure": null,
    "p-myProduct123-cleaning-0-language": null,
    "p-myProduct123-cleaning-0-value": "changable",
    "p-myProduct123-cleaning-1-unit_of_measure": null,
    "p-myProduct123-cleaning-1-language": null,
    "p-myProduct123-cleaning-1-value": "washable",
    "p-myProduct123-cleaning-id": "cleaning",
    "p-myProduct123-cleaning-kind": "option",
    "p-myProduct123-how_to_purchase-0-unit_of_measure": null,
    "p-myProduct123-how_to_purchase-0-language": null,
    "p-myProduct123-how_to_purchase-0-value": "myTest",
    "p-myProduct123-how_to_purchase-id": "how_to_purchase",
    "p-myProduct123-how_to_purchase-kind": "String",
    "p-myProduct123-title": "myTitle",
    "p-myProduct123-category": "47114712",
    "i-4711-timestamp-0-unit_of_measure": null,
    "i-4711-timestamp-0-language": null,
    "i-4711-timestamp-0-value": "2024-01-25T09:11:49",
    "i-4711-timestamp-id": "timestamp",
    "i-4711-timestamp-kind": "String",
    "i-4711-weight-0-unit_of_measure": "kg",
    "i-4711-weight-0-language": null,
    "i-4711-weight-0-value": 0.47,
    "i-4711-weight-id": "weight",
    "i-4711-weight-kind": "float",
    "i-4711-colour-0-unit_of_measure": null,
    "i-4711-colour-0-language": null,
    "i-4711-colour-0-value": "orange",
    "i-4711-colour-1-unit_of_measure": null,
    "i-4711-colour-1-language": null,
    "i-4711-colour-1-value": "black",
    "i-4711-colour-id": "colour",
    "i-4711-colour-kind": "option",
    "i-4712-timestamp-0-unit_of_measure": null,
    "i-4712-timestamp-0-language": null,
    "i-4712-timestamp-0-value": "2024-01-25T09:11:49",
    "i-4712-timestamp-id": "timestamp",
    "i-4712-timestamp-kind": "String",
    "i-4712-valid-0-unit_of_measure": null,
    "i-4712-valid-0-language": null,
    "i-4712-valid-0-value": true,
    "i-4712-valid-id": "valid",
    "i-4712-valid-kind": "boolean"
}

Any help would be highly appreciated

Until know, I am only able, to flatten the fields on a higher level.

[
  {
    "operation": "shift",
    "spec": {
      "product": {
        "product_number": "@(1,product_number).product_number",
        "title": "@(1,product_number).title",
        "items": "@(1,product_number).items",
        "category": {
          "id": "@(2,product_number).category"
        },
        "attributes": "@(1,product_number).product_attributes"
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "*": {
        "*": "p-&1-&"
      }
    }
  }
]
Solution

  • The JSON principally has two main branches attributes and items nested within the object product, and you wanted to alias them p and i respectively.

    Considering those facts you might use the following transformation with identifier value suffixes of the ampersands ( such as &1, &2 , &3 ..etc. ) are set by their depth levels within the tree :

    [
  {
    "operation": "shift",
    "spec": {
      "product": {
        "attributes": {
          "*": {
            "values": {
              "*": {
                "*": {
                  "@": "p.@(6,product_number).@(4,id).&2-&"
                }
              }
            },
            "*": "p.@(3,product_number).@(1,id).&1-&"
          }
        },
        "items": {
          "*": {
            "attributes": {
              "*": {
                "values": {
                  "*": {
                    "@": "i.@(5,item_number).@(3,id).&1"
                  }
                },
                "*": "i.@(3,item_number).@(1,id).&1-&"
              }
            }
          }
        }
      }
    }
  },
  {
    "operation": "shift",
    "spec": {
      "p": {
        "*": {
          "*": {
            "*": "&3-&2-&1-&"
          }
        }
      },
      "i": {
        "*": {
          "*": {
            "*": {
              "*": "&4-&3-&2-&1-&"
            }
          }
        }
      }
    }
  }
]

    the demo on the site https://jolt-demo.appspot.com/ is :

    enter image description here