0

As I googled and found a stack overflow post sql - How can I import a JSON file into PostgreSQL? - Stack Overflow, the post is for psql \copy command but not for pgadmin, plpgsql.

So here's the sample json.

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

How can I import this kind of json file on pgadmin/plpgsql?

(edit/update 2021/5/24)

A part of the json actually I'm trying to insert is here:

{"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": ""}

I will should be get a table that imported the data, something like this:

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

From plpgsql I mean, I didn't like to use psql command but wanted to script sql or plpgsql in pgadmin, so it can be fully automated for a hundreds of these json file data.

ー PupSoZeyDe ー
  • 1,082
  • 3
  • 14
  • 33
  • `insert into some_table(json_field) values('{"id": 23635,"name": "Jerry Green","comment": "Imported from facebook."}'`. – Adrian Klaver May 23 '21 at 14:43
  • Thanks. but I said "from file". The json file is tens of mega byte, copy and paste into the "value" should not be a good way. – ー PupSoZeyDe ー May 23 '21 at 14:49
  • This is the sort of information we need more of. Also: 1) Is each row going into a single `JSON` field or being split up into multiple fields? 2) When you said `plpgsql` do you mean you want to build a function that does this? 3) Does the file just have rows of `JSON` data only? Add the answers as an update to your question. – Adrian Klaver May 23 '21 at 15:56

1 Answers1

1

Since your file of JSON seems to already by in one line per record format, You can use the "import/export" feature of pgadmin4. However, that will just call psql behind the scenes, so you might as well cut out the middleman and use psql yourself.

jjanes
  • 37,812
  • 5
  • 27
  • 34