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


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