时间戳

首页 > 解决方案 > 在 pgadmin 上将单个 JSON 行从文件导入 PostgreSQL？

sql - 在 pgadmin 上将单个 JSON 行从文件导入 PostgreSQL？

问题描述

当我搜索并发现堆栈溢出后sql - 如何将 JSON 文件导入 PostgreSQL？- 堆栈溢出，该帖子适用于 psql\copy命令，但不适用于 pgadmin、plpgsql。

所以这里是示例 json。

{"id": 23635,"name": "Jerry Green","comment": "Imported from facebook."}
{"id": 23636,"name": "John Wayne","comment": "Imported from facebook."}

如何在 pgadmin/plpgsql 上导入这种 json 文件？

（编辑/更新 2021/5/24）

实际上我试图插入的 json 的一部分在这里：

{"id": 1396415271359897603, "conversation_id": "1396415271359897603", "created_at": "2021-05-23 19:38:39 JST", "date": "2021-05-23", "time": "19:38:39", "timezone": "+0900", "user_id": 978732571738755072, "username": "themooncarl", "name": "The Moon ", "place": "", "tweet": "@elonmusk is still on our side.  t.co/K5DnByjzic", "language": "en", "mentions": [], "urls": [], "photos": ["https://pbs.twimg.com/media/E2EQSZgWQAELw9T.jpg"], "replies_count": 78, "retweets_count": 47, "likes_count": 570, "hashtags": [], "cashtags": [], "link": "https://twitter.com/TheMoonCarl/status/1396415271359897603", "retweet": false, "quote_url": "", "video": 1, "thumbnail": "https://pbs.twimg.com/media/E2EQSZgWQAELw9T.jpg", "near": "", "geo": "", "source": "", "user_rt_id": "", "user_rt": "", "retweet_id": "", "reply_to": [], "retweet_date": "", "translate": "", "trans_src": "", "trans_dest": ""}
{"id": 1396414423057711109, "conversation_id": "1396414423057711109", "created_at": "2021-05-23 19:35:17 JST", "date": "2021-05-23", "time": "19:35:17", "timezone": "+0900", "user_id": 978732571738755072, "username": "themooncarl", "name": "The Moon ", "place": "", "tweet": "Me watching Bitcoin go down but realizing that it’s just a nice opportunity to buy more for cheap.  t.co/GkmSEPmJCh", "language": "en", "mentions": [], "urls": [], "photos": ["https://pbs.twimg.com/media/E2EPg4ZXMAMIXjJ.jpg"], "replies_count": 94, "retweets_count": 34, "likes_count": 771, "hashtags": [], "cashtags": [], "link": "https://twitter.com/TheMoonCarl/status/1396414423057711109", "retweet": false, "quote_url": "", "video": 1, "thumbnail": "https://pbs.twimg.com/media/E2EPg4ZXMAMIXjJ.jpg", "near": "", "geo": "", "source": "", "user_rt_id": "", "user_rt": "", "retweet_id": "", "reply_to": [], "retweet_date": "", "translate": "", "trans_src": "", "trans_dest": ""}
{"id": 1396388111840645120, "conversation_id": "1396388111840645120", "created_at": "2021-05-23 17:50:44 JST", "date": "2021-05-23", "time": "17:50:44", "timezone": "+0900", "user_id": 978732571738755072, "username": "themooncarl", "name": "The Moon ", "place": "", "tweet": "HODL!!! ", "language": "cs", "mentions": [], "urls": [], "photos": [], "replies_count": 263, "retweets_count": 149, "likes_count": 2299, "hashtags": [], "cashtags": [], "link": "https://twitter.com/TheMoonCarl/status/1396388111840645120", "retweet": false, "quote_url": "", "video": 0, "thumbnail": "", "near": "", "geo": "", "source": "", "user_rt_id": "", "user_rt": "", "retweet_id": "", "reply_to": [], "retweet_date": "", "translate": "", "trans_src": "", "trans_dest": ""}

我应该得到一个导入数据的表，如下所示：

pk|id|created_at|date|time|username|tweet
1,1396415271359897603,..
2,1396414423057711109,..
3,1396388111840645120,..

我的意思是，我不喜欢使用 psql 命令，但想在plpgsqlpgadmin 中编写 sql 或 plpgsql 脚本，因此它可以完全自动化数百个这些 json 文件数据。

标签： sqlpostgresqlpgadmin

解决方案

由于您的 JSON 文件似乎已经在每个记录格式一行，您可以使用 pgadmin4 的“导入/导出”功能。但是，这只会psql在幕后调用，因此您不妨去掉中间人并自己使用 psql。

推荐阅读