donk68254 2018-06-28 03:00
浏览 846
已采纳

如何将Json数据导入Postgres表

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?

  • 写回答

2条回答 默认 最新

  • dongluan0020 2018-06-28 04:03
    关注

    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)
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 MySQL数据库安装时出现这些问题怎么办
  • ¥20 请用MATLAB画含有二重积分和累加求和的随时间t变化的函数图像,不知如何把二重积分加到循环里,请解答一下
  • ¥15 kali终端update时出现这样的情况
  • ¥15 matlab调用stl文件时报错说内存不足
  • ¥20 医学图像格式、医学图像后处理
  • ¥15 MATLAB中的矩阵分离问题
  • ¥15 服务端可运行语言,解析unity文件
  • ¥15 英飞凌TC387使用MCAL唤醒TJA1145问题
  • ¥15 android tv图标显示异常
  • ¥20 (标签-AR|关键词-预测分析)