jsonjolt

Store numbers keys as values with new key name in JOLT

Source JSON:

{
  "data": {
    "campaign": {
      "104": {
        "day": {
          "2024-07-01": {
            "metrics": {
              "reach_impression": "6.0000000",
              "unique_impression": 6,
              "impression": 9
            }
          },
          "2024-07-03": {
            "metrics": {
              "reach_impression": "19.0000000",
              "unique_click": 1,
              "reach_click": "2.0000000"
            }
          }
        }
      },
      "106": {
        "day": {
          "2024-07-01": {
            "metrics": {
              "unique_click": 29,
              "reach_click": "29.0000000",
              "click": 35
            }
          }
        }
      },
      "124": {
        "day": {
          "2024-07-01": {
            "metrics": {
              "unique_impression": 472,
              "event": 1,
              "unique_click": 25,
              "click": 49,
              "progress_100": 1,
              "impression": 690
            }
          },
          "2024-07-03": {
            "metrics": {
              "unique_click": 14,
              "reach_click": "52.0000000",
              "click": 33,
              "unique_impression": 28,
              "impression": 32
            }
          }
        }
      }
    },
    "metrics": {
      "click_average": 22107.6666666667
    }
  },
  "metadata": {
    "campaign": {
      "104": {
        "channel_id": 1,
        "real_end_date": null,
        "channel_label": "Display",
        "real_start_date": "2024-01-11 21:32:25",
        "label": "5ka_Promo_veer_2024"
      },
      "124": {
        "channel_id": 1,
        "real_end_date": null,
        "channel_label": "Display",
        "real_start_date": "2024-06-04 21:34:23",
        "label": " X5Club_Evergreen_June24"
      }
    }
  }
}

Under data.campaign there are some numbers like 124, 104, 106. These numbers - ids of campaigns. I need to store these values inside field id, then we have field day and inside this field values like 2024-07-01 and i want to store these values as report_date field. Also under this level we have metrics object. Inside this object I want to grab all key-value pairs.

Main problem for me, how to store just numbers as keys in the right way.

Expected JSON:

[
  {
    "id": 124,
    "report_date": "2024-07-01",
    "unique_impression": 472,
    "event": 1,
    "unique_click": 25,
    "click": 49,
    "progress_100": 1,
    "impression": 690
  },
  {
    "id": 124,
    "report_date": "2024-07-03",
    "unique_click": 14,
    "reach_click": "52.0000000",
    "click": 33,
    "unique_impression": 28,
    "impression": 32
  },
  {
    "id": 104,
    "report_date": "2024-07-01",
    "reach_impression": "6.0000000",
    "unique_impression": 6,
    "impression": 9
  },
  {
    "id": 104,
    "report_date": "2024-07-03",
    "reach_impression": "19.0000000",
    "unique_click": 1,
    "reach_click": "2.0000000"
  },
  {
    "id": 106,
    "report_date": "2024-07-01",
    "unique_click": 29,
    "reach_click": "29.0000000",
    "click": 35
  }
]

UPDATE I also want to add field label from metadata object. I should match id from data.campaign.[number id] with metadata.campaign.[number id] and call it campaign_name

Expect:

[
  {
    "id": 124,
    "report_date": "2024-07-01",
    "unique_impression": 472,
    "event": 1,
    "unique_click": 25,
    "click": 49,
    "progress_100": 1,
    "impression": 690,
    "campaign_name": "X5Club_Evergreen_June24"
  },
  {
//etc
Solution

  • You can use the following transfomation in which $ wildcards(on the left hand side(s)) replicate the object keys taken from their respective levels and constructs the key-value pairs through the leaf node literals stated on the right-hand-side(s) such as :

    [
  {
    "operation": "shift",
    "spec": {
      "data": {
        "campaign": {
          "*": {
            "day": {
              "*": {
                "metrics": {
                  "$3": "&4_&2.id",
                  "$1": "&4_&2.report_date",
                  "*": "&4_&2.&"
                }
              }
            }
          }
        }
      }
    }
  },
  {//make id values unquoted
    "operation": "modify-overwrite-beta",
    "spec": {
      "*": {
        "id": "=toInteger"
      }
    }
  },
  {//get rid of object keys
    "operation": "shift",
    "spec": {
      "*": "[]"
    }
  }
]

    Edit : Yes, it's possible, just add

    "@6,metadata.campaign.&3.label": "&4_&2.&5_name"

    pair into the "metrics" objects such as

    "metrics": {
             "$3": "&4_&2.id",
             "$1": "&4_&2.report_date",
             "*": "&4_&2.&",
             "@6,metadata.campaign.&3.label": "&4_&2.&5_name"
           }

    in which