Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
sudarshank
Contributor III
Contributor III

How I can extract the data from Json

How to extract the data from below Json. I want to fetch orderId along with f_code and in_Kg value in table format. How can I retrieve it.

[

 {

  "orderId": 1,

  "orderDetails": [

   {

    "f_code": 8,

    "fruit": "Apple",

    "In Kg": {

     "value": [

      "2"

     ]

    }

   },

   {

    "f_code": 5,

    "fruit": "Orange",

    "In Kg": {

     "value": [

      "1"

     ]

    }

   }

  ]

 },

 {

  "orderId": 2,

  "orderDetails": [

   {

    "f_code": 4,

    "fruit": "Mango",

    "In Kg": {

     "value": [

      "2"

     ]

    }

   },

   {

    "f_code": 2,

    "fruit": "Kiwi",

    "In Kg": {

     "value": [

      "1"

     ]

    }

   }

  ]

 }

]

The output should be in the format screenshot.

Labels (5)
5 Replies
Covid19
Creator
Creator

Hi @sudarshan khandu​,

I propose 2 solutions to extract the data.

0695b00000Z1JAMAA3.png0695b00000Z1JAqAAN.png0695b00000Z1JB0AAN.pngKind regards,

😷19.

sudarshank
Contributor III
Contributor III
Author

Hi @not specified not specified​ ,

Thanks for reply, Its working for above json.

 

I forgot to add a field price_perKg in json, after added that in json I am getting error. I think there is numeric document.("1" and "2").

 

(Error on line 1 of document : The content of elements must consist of well-formed character data or markup.)

 

newJson:

 

[

 {

  "orderId": 1,

  "orderDetails": [

   {

    "f_code": 8,

    "fruit": "Apple",

    "price_perKg": {

     "1": "$1",

     "2": "$1.5"

    },

    "In_Kg": {

     "value": [

      "2"

     ]

    }

   },

   {

    "f_code": 5,

    "fruit": "Orange",

    "In_Kg": {

     "value": [

      "1"

     ]

    }

   }

  ]

 },

 {

  "orderId": 2,

  "orderDetails": [

   {

    "f_code": 4,

    "fruit": "Mango",

    "In_Kg": {

     "value": [

      "2"

     ]

    }

   },

   {

    "f_code": 2,

    "fruit": "Kiwi",

    "In_Kg": {

     "value": [

      "1"

     ]

    }

   }

  ]

 }

]

 

 

sudarshank
Contributor III
Contributor III
Author

Hi @not specified not specified​,

can you please share the screenshot of tFixedFlowInput setting.?

Covid19
Creator
Creator

Hi @sudarshan khandu​,

Seems Talend doesnt like JSON fields that start with a number, so put a tReplace before the tExtractJSONFields to replace "1" ==>"p1"

 

"price_perKg": {

   "p1": "$1",

   "p2": "$1.5"

  }

0695b00000Z1OU2AAN.png0695b00000Z1OWhAAN.pngKind regards,

😷19.

Covid19
Creator
Creator

0695b00000Z1OaeAAF.png