Convert Normalized schema and merge JSON to an existing key-Value pair for NoSQL DBs using Python


 

In this blog post, we will be discussing how to merge a normalized data structure to a Key-Value pair with a JSON.

Let's pick a scenario with customer and their Orders

Table 1 – Customer and Order Relationship

 

The relationship between customer and order is 1..n. The order list below belongs to two different customers. The dataset in Table 2 is transformed and stored in the DB (either relational or NoSQL dbs) as mentioned in Table 3

 

Table 2 – Orders

 

Table 3 – Customer and Orders in key-value pair (JSON)

 

As customers place new orders, the dataset in Table 4 must be merged with the already existing JSON set to produce a table set as mentioned in Table 5.

 

Table 4 – New orders from Customer

 

Table 5 – Merged orders

 

import
pandas as pd, zlib, lzma

dfneworders = pd.read_csv('C:\Temp\Outputfile\Blog\Orders.txt')

 

dfpreviousordercopy = pd.read_csv('C:\Temp\Outputfile\Blog\Output_Json.txt',quotechar="'", sep = "|")

 

rows = []

_ = dfpreviousordercopy.apply(lambda rowval: rows.append(pd.read_json(rowval['JSON'], orient='records')), axis=1)

dfintegratedorders = pd.concat([pd.concat(rows[0:]), dfneworders])

dftransposed = pd.DataFrame(dfintegratedorders.groupby(['CustCode']).apply(lambda dfsub:     dfsub.to_json(orient='records'))).reset_index()

dftransposed.columns = ['CustCode','JSON']

dftransposed.to_csv('C:\Temp\Outputfile\Blog\Output_MergedFile_withJson.txt', index=False, quotechar="'", sep = "|")

 

The python script extracts from old JSON dataset and integrates new orders and rebuild the JSON.


Comments (0)

Skip to main content