AnsweredAssumed Answered

Python to Generate Dynamic Nested JSON String

Question asked by bbarton-esristaff on Mar 26, 2019
Latest reply on Apr 22, 2019 by bbarton-esristaff

Greetings, Using python and ArcPy search cursors, I've extracted list(s) of dictionaries containing normalized key value pairs originating from specific tables but residing in a denormalized database layer. In the same script, I am now creating a JSON string with an object containing field & value pair arrays [] that that are to contain the keys and values (key value pairs) I've already distilled into lists of dictionary structures. There is an 'inserts' object created for each source table that the data is distilled from so I end up with a structure having field & value arrays contained within objects contained within 'inserts' arrays contained within objects contained within 'tableInserts' arrays contained within the JSON object as follows: { "tableInserts": [ {"inserts": [ { 'fields":[], "values": []    }   ]    }     }. 

 

I've got my python script to kick out the structure I am after but I am having difficulty separating the key value pairs contained in my lists of dictionary structures into their respective field & value target JSON arrays. 

 

Python

dictNorm = {        
        'place': placeNorm,      
        'place_type': place_TypeNorm,      
        'place_nm': nameNorm,      
        'place_geom_p': geomNorm,   
     }

constJSON = []
i = 0
for key, val in dictNorm.iteritems():   
     constJSON.append({"table":key, "inserts":[]})   
     for p in val:      
          constJSON[i]["inserts"].append({"fields":p})   
     i += 1

 

Portion of produced JSON string structure:

 

{
    "table": "place_nm",
     "inserts": [
           

                              {
                                    "fields": [

                                          {
                                       "lu_stat_id": 1,
                                       "lu_use_desig_id": 1,
                                        "place_nm": "<name>"

                                          }
                                      ]
                                 }


                     ]

},

 

Whereas I need it to produce (specifically, breaking key value pairs being parsed from dictionaries into "fields": & "values": arrays):

{
   "table": "place_nm",
   "inserts": [
                              {
                                    "fields": [
                                       "lu_stat_id",
                                       "lu_use_desig_id",
                                        "place_nm"
                                      ],
                                       "values": [
                                             1,
                                             1,
                                             <name>
                                       ]
                                 }

                        ]
},

The challenge is that the dictNorm dictionary structure references lists of dictionaries for the individual key value pairs I want to separate out so I need to unpack key value pairs existing in dictionaries wrapped in lists that are inside of a dictionary. 

 

I'm thinking I need another for loop iterator below line 13 to parse through these sub lists of dictionaries called in the dictNorm structure which if you were to print out dictNorm, a specific dictionary contained within would look like: 

 

'place_nm': [

                        {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'National Monument'},
                        {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Joe's Bar'},
                        {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Pike's Peak'},
                        {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Niagara Falls'},
                        {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Dulles Airport'},
                        {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Bay Bridge'},
                        {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'Bagram Airfield'},
                        {'lu_stat_id': 1, 'lu_use_desig_id': 1, 'place_nm': u'McDonals'},

                     ]

 

 

Any assistance would be appreciated as I know I have a lot going on here before getting to the JSON construction piece. 

Outcomes