CODEWITHSUNDEEP
jsonpostgresqlimport
ranj10
ranj10

Reputation: 31

Import json file into postgres table with single json column

I have been trying to load a json file in postgres as a single json column.

Table: create table book(values json);

The file looks like this:

[
  {
    "isbn": "846896359-3",
    "title": "Jungle Book 2, The",
    "price": 22.05,
    "date": "12/28/2017",
    "authors": [
      {
        "first": "Marlène",
        "last": "Ashley",
        "age": 38
      },
      {
        "first": "Miléna",
        "last": "Finley",
        "age": 37
      },
      {
        "first": "Stévina",
        "last": "Bullus",
        "age": 44
      }
    ],
    "publisher": {
      "name": "Youspan",
      "address": {
        "street": "Iowa",
        "number": "853",
        "city": "München",
        "country": "Germany"
      },
      "phone": "361-191-8111"
    }
  },
  {
    "isbn": "558973823-7",
    "title": "Star Trek III: The Search for Spock",
    "price": 36.58,
    "date": "4/19/2017",
    "authors": [
      {
        "first": "Uò",
        "last": "Ibel",
        "age": 26
      },
      {
        "first": "Mélys",
        "last": "Grasner",
        "age": 36
      },
      {
        "first": "Mylène",
        "last": "Laven",
        "age": 40
      },
      {
        "first": "Pò",
        "last": "Lapsley",
        "age": 37
      }
    ],
    "publisher": {
      "name": "Chatterbridge",
      "address": {
        "street": "Dennis",
        "number": "1",
        "city": "São Tomé",
        "country": "Sao Tome and Principe"
      },
      "phone": "845-226-0017"
    }
  }
]

Tried the copy command but it throws a 'Token "" is invalid.' error. I have also tried a number of other solutions

Upvotes: 2

Views: 4908

Answers (1)

ranj10
ranj10

Reputation: 31

So, I finally got it to work. First I removed new line characters from the file using tr -d '\n' < yourfile.txt

Then I ran the following script:

create table Bookstemp(values text);                    

copy Bookstemp from 'BOOKS_DATANew.json';

create table books(valjson json);

Insert into books
select values
from   (
           select json_array_elements(replace(values,'\','\\')::json) as values 
           from   Bookstemp
       ) a;

Upvotes: 1

Related Questions