How to efficiently convert denormalized data to JSON?

  Kiến thức lập trình

I am learning django and couldn’t find an answer on this.

I am receiving data a query in denormalized form. Here is how it looks like.
|Store_id|Shelf_id|Product_id|Qty|
|——–|——–|———-|—|
|store1 |shelf1 |product1 |20 |
|store1 |shelf1 |product2 |24 |
|store1 |shelf2 |product1 |34 |
|store1 |shelf2 |product3 |12 |
|store2 |shelf1 |product3 |21 |
|store2 |shelf1 |product1 |46 |

and so on. There is one-to-many relationships from store to shelf to product models. I have written a foreach loop to go over the entire dataset and convert it to JSON like

[
  {"store1" : [
    {"shelf1" : [
      {"product1" : 20}, 
      {"product2" : 24}]
    }, 
    {"shelf1" : [...]}]
  },
  {"store2" : [...]},
]

Although, the foreach loop is pretty straightforward to write, it is pretty inefficient in the execution. For every record, it has to check if the store and shelf exists before adding the product entry. The production data is expected to have 100+ stores and 5000+ shelves and be about 1.5MM rows per day. That is a large number of key lookups. Is there a way one can generate the data in JSON format directly out of django queries?

LEAVE A COMMENT