json - How to do a SQL like INNER JOIN on multiple Python dictionaries -

i planning out django app allows users not build custom tables associated models (e.g., user create trivial custom "parking spot" table associated "employee" model without having edit models.py), build custom reports using custom tables. way can think having model stores custom table data in jsonfield (i'm using postgres backend works out great), , have reports model allows users build , save "sql-like" queries return joined datasets custom reports.

i've figured out how store custom tables , use them in app, , have loose concept on how merge multiple json objects on pseudo foreign keys pulled custom reports, have gotten far creating one-to-one joins.

with script below, if of dicts have multiple records on single foreign key last record used. have idea how can accomplish one-to-many join of multiple python dictionaries?

if have these 3 datasets:

employees = [{"id": 1, "user_id": 303, "name": "mike"},              {"id": 2, "user_id": 304, "name": "james"},              {"id": 3, "user_id": 305, "name": "david"},]  roles = [{"id": 1, "user_id": 303, "role": "manager"},          {"id": 2, "user_id": 304, "role": "assistant"},          {"id": 3, "user_id": 305, "role": "assistant"},]  absences = [{"id": 1, "user_id": 303, "date": "2015-03-01"},             {"id": 2, "user_id": 303, "date": "2015-03-02"},             {"id": 3, "user_id": 303, "date": "2015-03-03"},             {"id": 4, "user_id": 304, "date": "2015-03-15"},             {"id": 5, "user_id": 305, "date": "2015-03-19"},] 

my desired outcome on straight join be:

[{'date': '2015-03-01', 'role': 'manager', 'user_id': 303, 'id': 1, 'name': 'mike'},     {'date': '2015-03-02', 'role': 'manager', 'user_id': 303, 'id': 1, 'name': 'mike'},     {'date': '2015-03-03', 'role': 'manager', 'user_id': 303, 'id': 1, 'name': 'mike'},      {'date': '2015-03-15', 'role': 'assistant', 'user_id': 304, 'id': 2, 'name': 'james'},      {'date': '2015-03-19', 'role': 'assistant', 'user_id': 305, 'id': 3, 'name': 'david'}] 

but since script loops through dictionary first (in case, employees), able this:

[{'date': '2015-03-03', 'role': 'manager', 'user_id': 303, 'id': 1, 'name': 'mike'}, {'date': '2015-03-15', 'role': 'assistant', 'user_id': 304, 'id': 2, 'name': 'james'}, {'date': '2015-03-19', 'role': 'assistant', 'user_id': 305, 'id': 3, 'name': 'david'}] 

and here basics of code:

def joiner(from_table, joins):     report_data = []      row in from_table:         new_row = row         table in joins:             table_dict = table["table"]             table_fk = table["fk"]             tdr in table_dict:                 if tdr[table_fk] == row[table_fk]:                     field in table["fields"]:                         new_row[field] = tdr[field]     report_data = from_table     return report_data  join_tables = [{"table": roles, "fk": "user_id", "fields": ["role"]},                {"table": absences, "fk": "user_id", "fields": ["date"]},           ] joiner(employees, join_tables) 

the simplest fix think of start "absences" dict from_table instead of employees, many-to-one join, limiting purposes.

also, if has better idea building user created data schemas can merged in custom reports using django, i'm ears. other solution can think of bypass django models entirely , have custom tables created, updated, , queried using straight sql.

as long put longest list of dictionaries first (can modified) when call merge, here crude solution

    def merge_lists(listdict1, listdict2,listdict3, joinkey):         mergedlist=listdict1         in range(len(listdict1)):             j in range(len(listdict2)):                 if (listdict1[i][joinkey]==listdict2[j][joinkey]):                     keys in listdict2[j].keys():                         mergedlist[i][keys]=listdict2[j][keys]             k in range(len(listdict3)):                 if listdict1[i][joinkey]==listdict3[k][joinkey]:                     keys in listdict3[k].keys():                         mergedlist[i][keys]=listdict3[k][keys]                                return mergedlist      merge_lists(absences, employees, roles,  "user_id")   [      {         "date":"2015-03-01",       "id":1,       "name":"mike",       "role":"manager",       "user_id":303    },    {         "date":"2015-03-02",       "id":1,       "name":"mike",       "role":"manager",       "user_id":303    },    {         "date":"2015-03-03",       "id":1,       "name":"mike",       "role":"manager",       "user_id":303    },    {         "date":"2015-03-15",       "id":2,       "name":"james",       "role":"assistant",       "user_id":304    },    {         "date":"2015-03-19",       "id":3,       "name":"david",       "role":"assistant",       "user_id":305    } ]