CODEWITHSUNDEEP
jsonpostgresql
Maliari Mohammed
Maliari Mohammed

Reputation: 67

How to import json file in postgres

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.

Upvotes: 0

Views: 311

Answers (1)

Anthony Sotolongo
Anthony Sotolongo

Reputation: 1648

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

Upvotes: 1

Related Questions