CODEWITHSUNDEEP
jsonpostgresql
mugizico
mugizico

Reputation: 11

Adding multiple JSON objects into Postgresql

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.

Upvotes: 0

Views: 4308

Answers (1)

mugizico
mugizico

Reputation: 11

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")

Upvotes: 1

Related Questions