如何一次将多个JSON文件插入到postgresql表中? [英] How to insert multiple JSON files into postgresql table at a time?
问题描述
我有多个JSON文件,它们都具有相同的格式,但根据每个事务其值是不同的。我想将此数据迁移到postgresql表。进行此操作的最佳方法是什么?
I have multiple JSON files, they all have same format but the values are different based on each transaction. I want to migrate this data to a postgresql table. What is the best way to proceed with this?
现在,我正在使用以下查询:
Right now, I am using the following query:
CREATE TABLE TEST (MULTIPROCESS VARCHAR(20), HTTP_REFERER VARCHAR(50));
INSERT INTO TEST SELECT MULTIPROCESS, HTTP_REFERER FROM json_populate_record(NULL::test, '{"multiprocess": true,"http_referer": "http://localhost:9000/"}');
但是,一旦文件数量变大,使用此技术将变得非常困难。还有其他有效方法吗?
But, once the number of files become large, it becomes very difficult to use this technique. Is there any other way to do this effectively?
推荐答案
您可以使用横向联接可一次插入多个行:
You could use a LATERAL JOIN to do insert more than one row at a time:
WITH
json AS(
VALUES('{"multiprocess": true,"http_referer":"http://localhost:9000"}')
,('{"multiprocess": false,"http_referer": "http://localhost:9001/"}')
,('{"multiprocess": true,"http_referer": "http://localhost:9002/"}')
) INSERT INTO test
SELECT multiprocess, http_referer
FROM json, LATERAL json_populate_record(NULL::test, json.column1::json);
或者您可以先插入到临时表中,然后再填充其他表。
Or you could insert into a staging table first and then populate your other table.
这篇关于如何一次将多个JSON文件插入到postgresql表中?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!