Import data from json file into Postgresql table

data.json

{
  "data":[
    {
      "city_code": "411800",
      "domain": "jiyuan.gov.cn",
      "id": 11849
    },
    {
      "city_code": "410500",
      "domain": "anyang.gov.cn",
      "id": 11852
    },
    {
      "city_code": "410500",
      "domain": "anyang.gov.cn",
      "id": 11846
    }
  ],
  "filter_count": 6520,
  "total_count": 6520
}

import_data_pg.md

prepare data format

1. convert data to one line tr -d '\n' filename or perl -p -e 's/\n//' filename or vim filename; :%j ; :wq
2. convert escape character
   sed -i 's/\\/\\\\/g' filename
3. delete tab or space sed -i 's/ //g;s/\t//g' filename or perl -p -e 's/ //g;s/\t//g'

create temp table and copy data to temp table

create temp table temp_data(v text);

copy temp_data from 'filepath';

copy data from temp table insert real table

insert into <realtable> select u->>'city_code', u->>'domain', u->>'id' from (select json_array_elements((v::json->>'data')::json) as u from temp_data) t