Python nested json

Question

Can any one have solution for this, i want there should be api data in this manner ?? I wanted api data in for similar state comes in one hood rather than seprate, different state data can be different obj,

    data = [{
        state_name:New_jersi, data:{
            category:Phishing,
            sub_cat_data:[{
                name:SubCat1,
                count:20
            },
            {
                name:SubCat2,
                count:30
            }]
        }
            category: malware,
            sub_cat_data:[{
                name:SubCat1,
                count:20
            },
            {
                name:SubCat2,
                count:30
            }]
    
    },
{
    state_name:Washinton, data:{
        category:Phishing,
        data:[{
            name:SubCat1,
            count:20
        },
        {
            name:SubCat2,
            count:30
        }]
    }
}]

But may api response be:


 {
            "state": "South Carolina",
            "state_count": 2,
            "Website Compromise/Intrusion": {
                "sub_category": {
                    "Insecure Direct Object Reference": 2,
                    "Memory Corruption": 2,
                    "SQLI": 1,
                    "Stack Overflow": 1,
                    "XSRF": 1,
                    "Heap Overflow": 1,
                    "Security Misconfiguration": 1
                }
            }
        },
        {
            "state": "South Carolina",
            "state_count": 1,
            "Phishing": {
                "sub_category": {
                    "Spear Phishing Attacks": 2,
                    "Fast Flux": 2,
                    "Rock fish": 2,
                    "Identify Theft/Social Engineering": 1,
                    "Phishing Redirector": 1,
                    "Pharming": 1,
                    "Exploitation of Hardware Vulnerability": 1
                }
            }
        },

i wanted same state data be in same object buut in my case state data comes in seprate object because of data comes through category, rather that seprate.

My logic are below

            cat_count = incnum.values('incident_category__cat_name','incident_category__cat_id').annotate(count=Count('incident_category__cat_id'))
            subcat_count = incnum.values('incident_sub_category__sub_cat_name','incident_sub_category__cat_id','incident_sub_category__id').annotate(count=Count('incident_sub_category__cat_id'))
            
            reporter_state_count1 = incnum.values('incident_category__cat_id','reporter__comp_individual_state','reporter__comp_individual_state__name').annotate(count=Count('incident_category__cat_id'))



            for x, state_ in enumerate(reporter_state_count1):
                for i, cat_ in enumerate(cat_count):
                    if state_['incident_category__cat_id'] == cat_['incident_category__cat_id']:
                        for i, cat_ in enumerate(cat_count):
                            if state_['incident_category__cat_id'] == cat_['incident_category__cat_id']:
                                arr16.append({'state':state_['reporter__comp_individual_state__name'], 'state_count':state_['count'], cat_['incident_category__cat_name']:{'sub_category':{}}})
                                for sub_ in subcat_count:
                                    if cat_['incident_category__cat_id'] == sub_['incident_sub_category__cat_id']:
                                        arr16[i][cat_['incident_category__cat_name']]['sub_category'].update({sub_['incident_sub_category__sub_cat_name']:sub_['count']})

Answer 1

cat_count = incnum.values('incident_category__cat_name', 'incident_category__cat_id').annotate(
    count=Count('incident_category__cat_id'))
subcat_count = incnum.values('incident_sub_category__sub_cat_name', 'incident_sub_category__cat_id',
                             'incident_sub_category__id').annotate(count=Count('incident_sub_category__cat_id'))

reporter_state_count1 = incnum.values('incident_category__cat_id', 'reporter__comp_individual_state',
                                      'reporter__comp_individual_state__name').annotate(
    count=Count('incident_category__cat_id'))

arr16 = []
for state_ in reporter_state_count1:
    state_data = {"state_name" : state_['reporter__comp_individual_state__name'], "data":[]}
    for cat_ in cat_count:
        if state_['incident_category__cat_id'] == cat_['incident_category__cat_id']:
            sub_cat_data = [{sub_['incident_sub_category__sub_cat_name']: sub_['count']} for sub_ in subcat_count if cat_['incident_category__cat_id'] == sub_['incident_sub_category__cat_id']]
            category_data = {"category": cat_['incident_category__cat_name'], "sub_cat_data": sub_cat_data}
            state_data["data"].append(category_data)

    arr16.append(state_data)

1 State might have multiple category, the way you are trying to make your api, it won't be able to show multiple category for a state. This is why i modify a little bit. you will find all the category in state object

Edit

Creating a dictionary which will store category_id as key and all the subcategory of that category as value

cat_to_subcat_list = {}
for cat_ in cat_count:
    sub_cat_data = [{"name":sub_['incident_sub_category__sub_cat_name'],"count": sub_['count']} for sub_ in subcat_count if
                    cat_['incident_category__cat_id'] == sub_['incident_sub_category__cat_id']]
    cat_to_subcat_list[cat_['incident_category__cat_id']] = {"category": cat_['incident_category__cat_name'], "sub_cat_data": sub_cat_data}

Createing a dictionary which will store state__name as key and a list of category object will save as value

state_data = {}
for state_ in reporter_state_count1:
    if state_['reporter__comp_individual_state__name'] not in state_data:
        '''This if statement is checking whether state_name exit or not.
         if state_name does not exist in dictionary it'll create a empty list  as it's value'''
        state_data[state_['reporter__comp_individual_state__name']] = []
    state_data[state_['reporter__comp_individual_state__name']].append(cat_to_subcat_list[state_['incident_category__cat_id']])

Re-formatting json as api needed

arr16 = [
    {
        "state_name": state_name,
        "data": state_data

    }for state_name, state_data in state_data.items()
]