Inserting array containing json objects as rows in postgres 9.5

Question

Just started using PostgreSQL 9.5 and have ran into my first problem with jsonb column. I have been trying to find an answer to this for a while but failing badly. Can someone help?

I have a json array in python containing json objects like this:

[{"name":"foo", "age":"18"}, {"name":"bar", "age":"18"}]

I'm trying to insert this into a jsonb column like this:

COPY person(person_jsonb) FROM '/path/to/my/json/file.json';

But only 1 row gets inserted. I hope to have each json object in the array as a new row like this:

1. {"name":"foo", "age":"18"}
2. {"name":"bar", "age":"18"}

Also tried:

INSERT INTO person(person_jsonb)
                VALUES (%s)
                ,(json.dumps(data['person'])

Still only one row gets inserted. Can someone please help??

EDIT: Added python code as requested

import psycopg2, sys, json

con = None
orders_file_path = '/path/to/my/json/person.json'

try:

    with open(orders_file_path) as data_file:
        data = json.load(data_file)
    con = psycopg2.connect(...)
    cur = con.cursor()
    person = data['person']
    cur.execute("""
                    INSERT INTO orders(orders_jsonb)
                    VALUES (%s)
                """, (json.dumps(person), ))
    con.commit()

    except psycopg2.DatabaseError, e:
        if con:
            con.rollback()

    finally:

        if con:
           con.close()

person.json file:

{"person":[{"name":"foo", "age":"18"}, {"name":"bar", "age":"18"}]}

Show some lines of the file you are copying from. Show the relevant code where the insert is tried. — Clodoaldo Neto
– Clodoaldo Neto, Commented Aug 31, 2016 at 13:40
Thanks for the feedback. I have made some edits to include further details :) — saan
– saan, Commented Aug 31, 2016 at 14:09

MatheusOl · Accepted Answer · 2016-08-31 13:53:26Z

6

Assuming the simplest schema:

CREATE TABLE test(data jsonb);

Option 1: parse the JSON in Python

You need to insert each row in PostgreSQL apart, you could parse the JSON on Python side and split the upper level array, then use cursor.executemany to execute the INSERT with each json data already split:

import json
import psycopg2

con = psycopg2.connect('...')

cur = con.cursor()

data = json.loads('[{"name":"foo", "age":"18"}, {"name":"bar", "age":"18"}]')

with con.cursor() as cur:
    cur.executemany('INSERT INTO test(data) VALUES(%s)', [(json.dumps(d),) for d in data])

con.commit()
con.close()

Option 2: parse the JSON in PostgreSQL

Another option is to push the JSON processing into PostgreSQL side using json_array_elements:

import psycopg2

con = psycopg2.connect('...')

cur = con.cursor()

data = '[{"name":"foo", "age":"18"}, {"name":"bar", "age":"18"}]'

with con.cursor() as cur:
    cur.execute('INSERT INTO test(data) SELECT * FROM json_array_elements(%s)', (data,))

con.commit()
con.close()

answered Aug 31, 2016 at 13:53

MatheusOl

12k3 gold badges33 silver badges29 bronze badges

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

1 Comment

kd88 Over a year ago

execute_batch (see here) will give better performance over executemany

Collectives™ on Stack Overflow

Inserting array containing json objects as rows in postgres 9.5

1 Answer 1

Option 1: parse the JSON in Python

Option 2: parse the JSON in PostgreSQL

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Option 1: parse the JSON in Python

Option 2: parse the JSON in PostgreSQL

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related