如何将Json数据导入Postgres表

Question

I have an API that gives json data text file called corporateHoliday.txt.

The data look like this:

{
    "holiday_id":"1712010100000104",
    "holiday_date":"2018-12-30 00:00:00.0",
    "holiday_description":"SUNDAY",
    "holiday_status":"A"
},
{
    "holiday_id":"1712010100000103",
    "holiday_date":"2018-12-29 00:00:00.0",
    "holiday_description":"SATURDAY",
    "holiday_status":"A"
}

My postgres table attributes look like this t_leave_holida(objectid, holiday_date,description,recstatus)

I want to import these json data from txt file into the postgres database table?

Answer 1

you can use jsonb type and just select data from json, like here:

db=# create table t_leave_holida(objectid bigint, holiday_date timestamp(1), description text,recstatus text);
db=# insert into t_leave_holida select (d->>'holiday_id')::bigint, (d->>'holiday_date')::timestamp, d->>'holiday_description', d->>'holiday_status' from (select '{
    "holiday_id":"1712010100000104",
    "holiday_date":"2018-12-30 00:00:00.0",
    "holiday_description":"SUNDAY",
    "holiday_status":"A"
}'::jsonb d) ali;
INSERT 0 1
db=# select * from t_leave_holida;
     objectid     |    holiday_date     | description | recstatus
------------------+---------------------+-------------+-----------
 1712010100000104 | 2018-12-30 00:00:00 | SUNDAY      | A
(1 row)