Loading json data from a file in Postgres

Question

I need to load data from multiple JSON files, each of which contains multiple entries in a Postgres table. I am using the following code but it is not working (I am using pgAdmin III for windows)

COPY tbl_staging_eventlog1 ("EId", "Category", "Mac", "Path", "ID") from 'C:\\SAMPLE.JSON' delimiter ',' ; 

The contents of the SAMPLE.JSON file are similar (giving two entries out of many):

 [{"EId":"104111","Category":"(0)","Mac":"ABV","Path":"C:\\Program Files (x86)\\Google","ID":"System.Byte[]"},{"EId":"104110","Category":"(0)","Mac":"BVC","Path":"C:\\Program Files (x86)\\Google","ID":"System.Byte[]"}] 

Answer 1

Try it:

 BEGIN; -- let create a temp table to bulk data into create temporary table temp_json (values text) on commit drop; copy temp_json from 'C:\SAMPLE.JSON'; -- uncomment the line above to insert records into your table -- insert into tbl_staging_eventlog1 ("EId", "Category", "Mac", "Path", "ID") select values->>'EId' as EId, values->>'Category' as Category, values->>'Mac' as Mac, values->>'Path' as Path, values->>'ID' as ID from ( select json_array_elements(replace(values,'\','\\')::json) as values from temp_json ) a; COMMIT;