Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

How to transfrom json using JoltTransformJSON porccessor?

Highlighted

How to transfrom json using JoltTransformJSON porccessor?

New Contributor

I have json data from a finance API, Alphavantage, i want to transform the format of the input JSON with JoltTransformJSON proccesor. I want the date as a field of the JSON object array.

InputJSON

{
  "Meta Data": {
    "1. Information": "Intraday (5min) open, high, low, close prices and volume",
    "2. Symbol": "MSFT",
    "3. Last Refreshed": "2018-12-07 16:00:00",
    "4. Interval": "5min",
    "5. Output Size": "Compact",
    "6. Time Zone": "US/Eastern"
  },
  "Time Series (5min)": {
    "2018-12-07 16:00:00": {
      "1. open": "104.7300",
      "2. high": "105.1300",
      "3. low": "104.7100",
      "4. close": "104.7900",
      "5. volume": "2218166"
    },
    "2018-12-07 15:55:00": {
      "1. open": "104.6100",
      "2. high": "105.0200",
      "3. low": "104.6100",
      "4. close": "104.7200",
      "5. volume": "1026715"
    }
  }
}

I would like a output like this:

OutputJSON

{
  "Meta Data": {
    "1. Information": "Intraday (5min) open, high, low, close prices and volume",
    "2. Symbol": "MSFT",
    "3. Last Refreshed": "2018-12-07 16:00:00",
    "4. Interval": "5min",
    "5. Output Size": "Compact",
    "6. Time Zone": "US/Eastern"
  },
  "Time Series (5min)": 
    [
     {
      "Date" : "2018-12-07 16:00:00",
      "1. open": "104.7300",
      "2. high": "105.1300",
      "3. low": "104.7100",
      "4. close": "104.7900",
      "5. volume": "2218166"
    },
    { 
      "Date" : "2018-12-07 15:55:00", 
      "1. open": "104.6100",
      "2. high": "105.0200",
      "3. low": "104.6100",
      "4. close": "104.7200",
      "5. volume": "1026715"
    }
  ]
}

How must be the JoltTransformJSON?

1 REPLY 1

Re: How to transfrom json using JoltTransformJSON porccessor?

New Contributor

Finally i could solve it bymyself. I share the transformation that i used in case can be useful for someone :)

[
  {
    "operation": "shift",
    "spec": {
      "Time Series (5min)": {
        "*": {
          "4. close": "&1.cotizacion",
          "5. volume": "&1.volumen",
          "value": "&1.Value",
          "$": "&1.Fecha"
        }
      }
    }
  }
]



This is the output JSON:

{
  "2018-12-07 16:00:00" : {
    "Fecha" : "2018-12-07 16:00:00",
    "cotizacion" : "104.7900",
    "volumen" : "2218166"
  },
  "2018-12-07 15:55:00" : {
    "Fecha" : "2018-12-07 15:55:00",
    "cotizacion" : "104.7200",
    "volumen" : "1026715"
  }
}