Convert sql join data into list of dictionaries on certain same key-CodePudding

From a sql stored proc that performs a join on two tables I get the data below.

[
    {"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 0, "part_number": "string", "part_description": "string"},
    {"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 1, "part_number": "string", "part_description": "string"},
    {"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 2, "part_number": "string", "part_description": "string"},
    {"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 3, "part_number": "string", "part_description": "string"}
]

What would be the best way to convert this data into the below format? Is it possible on the python side? Or is there something other than a join I can perform to get data back in this format?

[{
  "service_order_number": "ABC",
  "vendor_id": 0,
  "recipient_id": 0,
  items: [
    {
      "item_id": 0,
      "part_number": "string",
      "part_description": "string",
      
    },
{
      "item_id": 1,
      "part_number": "string",
      "part_description": "string",
     
    }
  ]
},

{"service_order_number": "DEF"
  "vendor_id": 0,
  "recipient_id": 0,
  items: [
    {
      "item_id": 2,
      "part_number": "string",
      "part_description": "string",
      
    },
{
      "item_id": 3,
      "part_number": "string",
      "part_description": "string",
     
    }
  ]
}]

CodePudding user response：

Yes, it's possible to convert the data on the python side. You can use a dictionary to group the items based on the service_order_number and then convert the dictionary to the desired format. Here is an example:

 data = [{"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 0, "part_number": "string", "part_description": "string"},{"service_order_number": "ABC", "vendor_id": 0, "recipient_id": 0, "item_id": 1, "part_number": "string", "part_description": "string"},{"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 2, "part_number": "string", "part_description": "string"},{"service_order_number": "DEF", "vendor_id": 0, "recipient_id": 0, "item_id": 3, "part_number": "string", "part_description": "string"}]

grouped_data = {}
for item in data:
    service_order_number = item['service_order_number']
    if service_order_number not in grouped_data:
        grouped_data[service_order_number] = {
            "service_order_number": service_order_number,
            "vendor_id": item["vendor_id"],
            "recipient_id": item["recipient_id"],
            "items": []
        }
    grouped_data[service_order_number]["items"].append({
        "item_id": item["item_id"],
        "part_number": item["part_number"],
        "part_description": item["part_description"]
    })

final_data = [value for key, value in grouped_data.items()]

print(final_data)

CodePudding user response：

Here a possibile solution using defaultdict()

from collections import defaultdict

grouped_data = defaultdict(list)
for item in data:
    grouped_data[item['service_order_number']].append(item)

res = [{'service_order_number': k, 'vendor_id': v[0]['vendor_id'],
    'recipient_id': v[0]['recipient_id'], 'items': [{'item_id': item['item_id'], 
    'part_number': item['part_number'], 'part_description': item['part_description']}
    for item in v]} for k, v in grouped_data.items()]
print(res)

CodePudding user response：

If you don't need the original data after, you can use dict.pop to create common keys to group over and populate a dictionary in a loop. Note that this code destroys the original data you'll only have res in the end.

res = {}
keys = ['service_order_number', 'vendor_id', 'recipient_id']
for d in my_data:
    vals = tuple(d.pop(k) for k in keys)
    res.setdefault(vals, {}).update(dict(zip(keys, vals)))
    # "items" key-value pairs are further nested inside 'items' key
    res[vals].setdefault('items', []).append(d)
res = list(res.values())

which outputs

[{'service_order_number': 'ABC',
  'vendor_id': 0,
  'recipient_id': 0,
  'items': [{'item_id': 0, 'part_number': 'string', 'part_description': 'string'},
            {'item_id': 1, 'part_number': 'string', 'part_description': 'string'}]},
 {'service_order_number': 'DEF', 
  'vendor_id': 0, 
  'recipient_id': 0,
  'items': [{'item_id': 2, 'part_number': 'string', 'part_description': 'string'},
            {'item_id': 3, 'part_number': 'string', 'part_description': 'string'}]}]