How to import json file in postgres

Question

I'm trying to import json file into a PostgreSQL. Example data:

 {
  "asin":"2094869245",
  "title":"5 LED Bicycle Rear Tail Red Bike Torch Laser Beam Lamp Light",
  "price":8.26,
  "imhUrl":"http://ecx.images-amazon.com/images/I/51RtwnJwtBL._SY300_.jpg"
 }

 {
  "asin":"7245456259",
  "title":"Black Mountain Products Single Resistance Band - Door Anchor,
  "price":10.49,
  "imhUrl":"http://ecx.images-amazon.com/images/I/411Ikpf122L._SY300_.jpg"
 }`

Would like the result to look like:

data
--------------------------------------------------------------------
{
  "asin":"2094869245",
  "title":"5 LED Bicycle Rear Tail Red Bike Torch Laser Beam Lamp Light",
  "price":8.26,
  "imhUrl":"http://ecx.images-amazon.com/images/I/51RtwnJwtBL._SY300_.jpg"
}

--------------------------------------------------------------------
{
  "asin":"7245456259",
  "title":"Black Mountain Products Single Resistance Band - Door Anchor,
  "price":10.49,
  "imhUrl":"http://ecx.images-amazon.com/images/I/411Ikpf122L._SY300_.jpg"
}

The data is type json.

My JSON FILE will be stored in a single JSON column called data.

So the file contains invalid JSON and you want to split it up in valid JSON values? — , Jul 02 '19 at 10:57
NO.i have json file valid and i want to import him in a single json column in postgres. — Maliari Mohammed, Jul 02 '19 at 11:04
Your first example is an invalid JSON value. Is that supposed to be two files or just one file? If it's one file, then how are the individual objects separated? — , Jul 02 '19 at 11:05
See e.g. [here](https://stackoverflow.com/questions/54612658/) and [here](https://stackoverflow.com/questions/39224382) — , Jul 02 '19 at 11:09
no is just one file and the individual objects are separated with {}. — Maliari Mohammed, Jul 02 '19 at 11:10
Then your example is missing a `,` between the values - as shown, this **is** invalid JSON. — , Jul 02 '19 at 11:11
okaay thank uu for your response. I tested the instructions of the two links but it did not work — Maliari Mohammed, Jul 02 '19 at 11:20

score 1 · Accepted Answer · answered Jul 03 '19 at 17:34

if you remove the newline in your JSON file like this:

{ "asin":"2094869245", "title":"5 LED Bicycle Rear Tail Red Bike Torch Laser Beam Lamp Light","price":8.26,  "imhUrl":"http://ecx.images-amazon.com/images/I/51RtwnJwtBL._SY300_.jpg"}
{ "asin":"7245456259",  "title":"Black Mountain Products Single Resistance Band - Door Anchor",  "price":10.49,  "imhUrl":"http://ecx.images-amazon.com/images/I/411Ikpf122L._SY300_.jpg" }

you can load to a table with copy command:

create table js (a json);

copy js from '/tmp/data.json'  DELIMITER '^'  CSV  QUOTE ''''  ESCAPE '\'

very helpful @anthony-sotolongo. worked perfectly. – smoore4 Jul 03 '19 at 17:57 — smoore4, Jul 03 '19 at 17:57

How to import json file in postgres

1 Answers1