Load JSON Data to PostgreSQL 11 Tables

Question

Need to load the following JSON to PostgreSQL-11 tables and need help with it:

[ { "id":"1", "name":"abc_xyz", "language":"English", "title":"facebook", "description":"This is a test", "categories":[ "https://facebook/category/28", "https://facebook/category/29", "https://facebook/category/30", "https://facebook/category/31" ] }, "id":"2", "name":"abc_xyz", "language":"French", "title":"Twitter", "description":"This is another test", "categories":[ "https://twitter/category/2", "https://twitter/category/23", "https://twitter/category/35" ] } ]

The JSON data needs to be loaded into two tables: TableA Columns:

id int, 
name varchar,
language varchar, 
description varchar

TableB Columns:

Association_Id serial,
TableA.Id int,
Category_Id int,
Last_Update_Time timestamp DEFAULT NOW()

TableA will have rows like :

id = 1,
name = abc_xyz,
language = English,
title = facebook,
description = This is a test

TableB Rows:

Association_Id = 1
TableA_Id = 1
Category_Id  = 28

Association_Id = 2
TableA_Id = 1
Category_Id  = 29

Association_Id = 3
TableA_Id = 1
Category_Id  = 30

etc etc.

Please help... Thanks in advance

You might want to take a look at: https://stackoverflow.com/questions/39224382/how-can-i-import-a-json-file-into-postgresql — Stephan Schlecht, Jun 12 '19 at 14:09
@StephanSchlecht -- Thanks.. I already looked at that example, however I have a second table to be populated as well from the same JSON. There are more than one Categories associated with the same entry in the JSON. I want to know how to populate the association table. — P_Ar, Jun 12 '19 at 14:20
One possibility that might or might not be considered is to write a small program in Java or C# or any other language you prefer. It is easy to de-serialize JSON and then you can populate the tables accordingly. — Stephan Schlecht, Jun 12 '19 at 14:59
@StephanSchlecht -- I was looking for a direct query from the DB which could do so, but seems like I will have to do so using a Java/python/C++program. Thank you — P_Ar, Jun 13 '19 at 13:15

Stephan Schlecht · Accepted Answer · 2019-06-13T16:34:42.373

Here's a little program. It is written in Java, but would work in a similar way in another language. It is completely self-contained. I tried to make it minimalistic, but it could still be a good starting point.

It does:

read in the JSON with the GSON library
Import of the data into the Postgres database via JDBC prepared statements

Result

With the psql command line program querying TableA and TableB returns:

stephan=# select * from TableA;                                                                                                
 id |  title   |  name   | language |     description      
----+----------+---------+----------+----------------------
  1 | facebook | abc_xyz | English  | This is a test
  2 | Twitter  | abc_xyz | French   | This is another test
(2 rows)

stephan=# select * from TableB;
 association_id | tablea_id | category_id |      last_update_time      
----------------+-----------+-------------+----------------------------
             29 |         1 |          28 | 2019-06-13 18:04:52.671833
             30 |         1 |          29 | 2019-06-13 18:04:52.671833
             31 |         1 |          30 | 2019-06-13 18:04:52.671833
             32 |         1 |          31 | 2019-06-13 18:04:52.671833
             33 |         2 |           2 | 2019-06-13 18:04:52.692635
             34 |         2 |          23 | 2019-06-13 18:04:52.692635
             35 |         2 |          35 | 2019-06-13 18:04:52.692635
(7 rows)

Java

import java.util.Properties;
import java.sql.*;
import com.google.gson.Gson;

class Entry {
    int id;
    String name;
    String language;
    String title;
    String description;
    String[] categories;
}


public class Main {

    public static void main(String[] args) {
        String json = "[{\"id\":\"1\",\"name\":\"abc_xyz\",\"language\":\"English\",\"title\":\"facebook\",\"description\":\"This is a test\",\"categories\":[\"https://facebook/category/28\",\"https://facebook/category/29\",\"https://facebook/category/30\",\"https://facebook/category/31\"]},{\"id\":\"2\",\"name\":\"abc_xyz\",\"language\":\"French\",\"title\":\"Twitter\",\"description\":\"This is another test\",\"categories\":[\"https://twitter/category/2\",\"https://twitter/category/23\",\"https://twitter/category/35\"]}]";
        try {
            Entry[] entries = readJSON(json);
            importIntoDB(entries);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private static Entry[] readJSON(String json) {
        Entry[] entries;
        Gson g = new Gson();
        entries = g.fromJson(json, Entry[].class);
        return entries;
    }

    private static Connection createConnection()
            throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost/stephan";
        Properties props = new Properties();
        props.setProperty("user", "stephan");
        props.setProperty("password", "secret");
        //props.setProperty("ssl", "true");
        return DriverManager.getConnection(url, props);
    }

    private static void importIntoDB(Entry[] entries)
            throws SQLException, ClassNotFoundException {
        Connection connection = createConnection();
        try (connection) {
            connection.setAutoCommit(false);
            PreparedStatement insertTableA = connection.prepareStatement(
                    "INSERT INTO TableA (id, name, language, title, description) VALUES(?, ?, ?, ?, ?)");
            PreparedStatement insertTableB = connection.prepareStatement(
                    "INSERT INTO TableB (TableA_Id, Category_Id) VALUES (?, ?)");
            for (Entry entry : entries) {
                insertTableA.setInt(1, entry.id);
                insertTableA.setString(2, entry.name);
                insertTableA.setString(3, entry.language);
                insertTableA.setString(4, entry.title);
                insertTableA.setString(5, entry.description);

                insertTableA.execute();

                for (String category : entry.categories) {
                    insertTableB.setInt(1, entry.id);
                    String categoryIdString = category.substring(category.lastIndexOf('/') + 1);
                    int categoryId = Integer.parseInt(categoryIdString);
                    insertTableB.setInt(2, categoryId);
                    insertTableB.execute();
                }
                connection.commit();
            }
            insertTableA.close();
            insertTableB.close();
        }
    }

}

Required Libraries

The above program requires the Postgres JDBC library and the GSON library for JSON deserialization.

You can download it from here:

https://jdbc.postgresql.org/download.html

http://central.maven.org/maven2/com/google/code/gson/gson/2.8.5/gson-2.8.5.jar

Batches

If you have to import a large amount of entries, you could think about combining the PreparedStatements into batches and then executing many statements at once, take a look at the methods addBatch and executeBatch.

Load JSON Data to PostgreSQL 11 Tables

1 Answers1