Import json file into postgres table with single json column

Question

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

create table Books(values json); copy Books from 'BOOKS_DATA.json'; The file is in the data folder of postgres. The command works if I make the 'values' column text but not if it of type json. — ranj10
– ranj10, Commented Apr 21, 2018 at 22:50
Try setting a delimiter explicitly: COPY books FROM 'BOOKS_DATA.json' DELIMITER '`' - sets it to a character that doesn't exist in your JSON, just to make sure it will treat the whole thing as one field rather than attempting to split it by some default character. — 404
– 404, Commented Apr 21, 2018 at 22:55
I edited my comment a couple times as it wasn't showing the character correctly, so make sure you're using what is currently shown - a delimiter of the ` (backtick) character in single quotes. I use that exact character all the time when copying to and from files, so I know it works. Alternatively put some other character in there - maybe | or something else that isn't used in your json. — 404
– 404, Commented Apr 21, 2018 at 22:59

lehnerchristian · Accepted Answer · 2019-03-22 12:43:31Z

1

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;

edited Mar 22, 2019 at 12:43

lehnerchristian

1,3073 gold badges13 silver badges26 bronze badges

answered Apr 21, 2018 at 23:29

ranj10

311 silver badge5 bronze badges

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

Collectives™ on Stack Overflow

Import json file into postgres table with single json column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related