2

I have a json file from which i want to put data in my postgres table. Link to file http://tempsend.com/0013F2BE73.
The structure of my json is like:

{"url": someurl,
 "hasMore": false, 
 "orderItems": [{"orderId": "OD003663828138911500", 
                 "status": "APPROVED", 
                 "subItems": [], 
                 "priceComponents": {"shippingCharge": 30.0, 
                                     "customerPrice": 417.0, 
                                     "totalPrice": 447.0,
                                     "sellingPrice": 439.0}, 
                 "orderItemId": 234207779, 
                 "listingId": "LSTNDNEFJK8E4YZEGPJP2EBHU", 
                 "title": "Masha Women's Nighty", 
                 "price": 447.0, 
                 "dispatchByDate": "2015-08-21T09:13:34+05:30", 
                 "shippingPincode": "700027", 
                 "fsn": "NDNEFJK8E4YZEGPJ", 
                 "sla": 2, 
                 "sku": "NT55-188", 
                 "updatedAt": "2015-08-17T09:13:37+05:30", 
                 "stateDocuments": [""], 
                 "shippingFee": 30.0, 
                 "hold": false,
                 "orderDate": "2015-08-17T09:12:46+05:30", 
                 "quantity": 1},

                 {"orderId":     ....

My database table structure is:

Create table If Not Exists temp_fk_sale_order(order_id varchar,
status varchar,sub_items varchar,shipping_charge float,customer_price float,
total_price float,selling_price float,order_item_id varchar,
listing_id varchar,title varchar,price float,dispatch_by_date timestamp,
shipping_pincode integer,fsn varchar,sla integer,sku varchar,updated_at timestamp,
state_documents varchar,shipping_fee varchar,hold varchar,order_date timestamp,
quantity integer)

I am on ubuntu 14.04 and file is in my downloads. how to insert data from this file to my postgres table?

EDIT:

I wrote a python code which parse the json and store the required values in different lists.

My code:

import json
import csv

'''Store data in a string and then convert it to json'''
with open('data.json','rb') as data:
    data_str = data.read()
data_dict = json.loads(data_str)

'''create a list of orders'''
orders_len = len(data_dict['orderItems'])

order_id = []
status = []
sub_items = []
shipping_charge = []
customer_price = []
total_price = []
selling_price = []
order_item_id = []
listing_id = []
title = []
price = []
dispatch_by_date = []
shipping_pincode = []
fsn = []
sla = []
sku = []
updated_at = []
state_documents = []
shipping_fee = []
hold = []
order_date = []
quantity = []

for num in range(0,orders_len):
    order_id.append(data_dict['orderItems'][num]['orderId'])
    status.append(data_dict['orderItems'][num]['status'])
    sub_items.append(data_dict['orderItems'][num]['subItems'])
    shipping_charge.append(data_dict['orderItems'][num]['priceComponents']['shippingCharge'])
    customer_price.append(data_dict['orderItems'][num]['priceComponents']['customerPrice'])
    total_price.append(data_dict['orderItems'][num]['priceComponents']['totalPrice'])
    selling_price.append(data_dict['orderItems'][num]['priceComponents']['sellingPrice'])
    order_item_id.append(data_dict['orderItems'][num]['orderItemId'])
    listing_id.append(data_dict['orderItems'][num]['listingId'])
    title.append(data_dict['orderItems'][num]['title'])
    price.append(data_dict['orderItems'][num]['price'])
    dispatch_by_date.append(data_dict['orderItems'][num]['dispatchByDate'])
    shipping_pincode.append(data_dict['orderItems'][num]['shippingPincode'])
    fsn.append(data_dict['orderItems'][num]['fsn'])
    sla.append(data_dict['orderItems'][num]['sla'])
    sku.append(data_dict['orderItems'][num]['sku'])
    updated_at.append(data_dict['orderItems'][num]['updatedAt'])
    state_documents.append(data_dict['orderItems'][num]['stateDocuments'])
    shipping_fee.append(data_dict['orderItems'][num]['shippingFee'])
    hold.append(data_dict['orderItems'][num]['hold'])
    order_date.append(data_dict['orderItems'][num]['orderDate'])
    quantity.append(data_dict['orderItems'][num]['quantity'])

I can connect to my database using psycopg2 but i need to pass these lists as parametres. How to do that?
Also if anyone can help me do that through a stored procedure? that would help reduce the load and dependency on the script.

1 Answer 1

2

PostgreSQL doesn't have functions to import raw json data directly from file. So you should write a php/python/etc script which reads row from json and insert it to PostgreSQL.

Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.