0

I have a nested json. I would like to extract the following values institution_id,investor_id,consultant_id .I cant seem to extract it properly. Please see below. Could anyone help please.

{
   instituition_id : 112,
   name : 'abc',
   invest_funds: [
     { 
          investor_id : 20,
          name: 'fake',
          consultants:[
             {
                consultant_id: 10,
                name: 'xyz', 
                consultant_offices: [
                   {
                       consultant_office_id : 1,
                       name: 'jkl',
                       consultant_contants : [
                         {
                            consultant_contact_id: 15,
                             another_id: 211,
                             asset_classes:[
                             {
                               val1: 'abc',
                               val2: 311,
                               val3: 'dfsd',
                               val4: 'fghfhg'
                               },
                             {
                               val1: 'def',
                               val2: 301,
                               val3: 'sedr',
                               val4: 'kugg'
                               },
                        
                           ]
                         }
                      ]
                   }
               ]
      },
      {
         consultant_id: 14,
         name: 'pqr',
         consultant_offices: [
      ]
    }
 ]
},
    { 
          investor_id : 28,
          name: 'fake1',
          consultants:[
             {
                consultant_id: 30,
                name: 'xyz1', 
                consultant_offices: [
                    
                 ]
              }
          ]
       }
     ]
   }

I am creating a mapping.

    {"unnest":true, "path":"invest_funds[?consultants]"},
    {"name":"institution_id", "path":"institution_id", "absolute": true},
    {"name":"investor_id", "path":"investor_id"},
    {"name":"consultant_id", "path":"consultants[?consultant_id]"}

my results:

institution_id  investor_id consultant_id
112                    20                       [10, 14]
112                    28                           [30]

desired

institution_id  investor_id consultant_id
112                    20                       10
112                    20                       14
112                    28                       30
SSK
  • 3,444
  • 6
  • 32
  • 59
Sam
  • 1
  • What are those `{"unnest":true, "path":"invest_funds[?consultants]"},`? This is not pure JMESPath? Pointing us at the tools your using here would be helpful. – β.εηοιτ.βε Mar 04 '21 at 18:53
  • "unnest" true means it is a nested json. "path" at the 1st line gives the parent path. line 2-4 are the objects from the nested json and their path. i am using this to write a config file which a python script reads and then converts my json to csv. – Sam Mar 05 '21 at 03:46

0 Answers0