Adding multiple JSON objects into Postgresql

Question

I have a json file containing hundreds of Json objects.

My postgresql table was created like this:

CREATE TABLE collections(
 id serial,
 collection json);

It can add one object at a time into the table using INSERT INTO collections (collection) values (json_object); but that's tedious and not sustainable. What would be a better way to do this?

One solution I found (as explained by this StackOverflow answer) was to create (1) create temporary table and bulk json data into it (2) create columns corresponding to keys and add values like so:

    create temporary table temp_json (values text) on commit drop;
copy temp_json from 'C:\SAMPLE.JSON';

-- remove this comment to insert records into your table
-- insert into tbl_staging_eventlog1 ("EId", "Category", "Mac", "Path", "ID") 

select values->>'EId' as EId,
       values->>'Category' as Category,
       values->>'Mac' as Mac,
       values->>'Path' as Path,
       values->>'ID' as ID      
from   (
           select json_array_elements(replace(values,'\','\\')::json) as values 
           from   temp_json
       ) a;

but this defeats the whole purpose of NoSQL. I merely just want to store an autoincrementing id with a json object on each row.

Does it fits your problem? http://stackoverflow.com/q/29497662/3937028 — antonio_antuan, Jul 08 '16 at 20:07
@antonio_antuan not really, I have one JSON file with objects like this `[{json_object1}, {json_object2},.....]` and I want to add them to my postgresql db as `row1: 1, json_object2 row2: 2 , json_object2..` keep in mind these are hundreds of json objects and increasing, otherwise I would ve continued doing it by hand. — mugizico, Jul 08 '16 at 20:16
Oh, ok, try to use this: http://adpgtech.blogspot.ru/2014/09/importing-json-data.html?m=1 — antonio_antuan, Jul 08 '16 at 20:33
Which operating system are you on? Do you have something like Python you can call from a command line? It can be done with a few typed lines, but they'll be system-specific. — Feneric, Jul 09 '16 at 02:12
@Feneric I am windows 7 entreprise. I was definitely thinking about using python. I am in the process of writing a script right now... — mugizico, Jul 11 '16 at 15:58

score 1 · Accepted Answer · answered Jul 11 '16 at 21:08

I figured out a way to do it in Python with the psycopg2 package if anyone is interested. just make sure to fill in the appropriate fields(database, username, password, etc..)

import psycopg2
import json


path_to_file = input('Enter path to json file:')
with open(path_to_file, 'r') as data_file:
    data = json.load(data_file)

collection_array = []
for item in data:
    collection_array.append(json.dumps(item))



try:
    conn = psycopg2.connect(database="", user="", password="",host="127.0.0.1",port="5433")
    print ("opened  database successfully")
    cur = conn.cursor()

    for element in collection_array:
        cur.execute("INSERT INTO my_table (json_column_name) VALUES (%s)", (element,))
    print("successfully inserted records")    


except psycopg2.Error as e:
    raise

finally:
    conn.commit()
    conn.close()
    print("connection is closed")

Cool, you beat me to it. That's very close though to what I was going to recommend. — Feneric, Jul 12 '16 at 01:29
@Feneric haha yea I decided to take a crack at it; wasn't hard at all especially with the awesome psycopg2 package. — mugizico, Jul 12 '16 at 15:41

Adding multiple JSON objects into Postgresql

1 Answers1