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? — user330315
– user330315, Commented Jul 2, 2019 at 10:57
NO.i have json file valid and i want to import him in a single json column in postgres. — Maliari Mohammed
– Maliari Mohammed, Commented Jul 2, 2019 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? — user330315
– user330315, Commented Jul 2, 2019 at 11:05
Then your example is missing a , between the values - as shown, this is invalid JSON. — user330315
– user330315, Commented Jul 2, 2019 at 11:11

Anthony Sotolongo · Accepted Answer · 2019-07-03 17:34:17Z

1

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 '\'

answered Jul 3, 2019 at 17:34

Anthony Sotolongo

1,6882 gold badges13 silver badges23 bronze badges

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

1 Comment

smoore4 Over a year ago

very helpful @anthony-sotolongo. worked perfectly.

Collectives™ on Stack Overflow

How to import json file in postgres

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related