PostgreSQL数据加载：使用COPY命令加载JSON数据

作者：弗朗西斯科·蒂西奥（Francesco Tisiot）

Francesco来自意大利维罗纳，在Aiven担任开发人员。作为一名多年的数据分析师，他有很多故事要讲，也有很多建议给各地的数据管理者。

原文链接：

https://dev.to/ftisiot/how-to-load-json-data-in-postgresql-with-the-the-copy-command-4gmh

图片来自网络：意大利维罗纳的沿河风光

维罗纳（Verona）是意大利北部威尼托大区的一座历史悠久的城市，位于阿尔卑斯山南麓，阿迪杰河（Adige River）畔，距离东边的著名水城威尼斯约114公里。它是维罗纳省的首府，以其丰富的古罗马遗迹、中世纪和文艺复兴时期的建筑而闻名于世。它还是莎士比亚经典悲剧《罗密欧与朱丽叶》的故事发生地，市内有一处据传为朱丽叶故居的地方，每年吸引大量游客参观。

您有一个 JSON 数据集，想要上传到包含格式正确的行和列的 PostgreSQL 表...如何操作？

这里有包括我的和网友的两篇博文，会告诉您将 JSON 加载到包含唯一JSON列的专用临时表中，然后解析它并加载到目标表中。然而，可能还有另一种方法，避免使用临时表！

两篇博文链接：

https://ftisiot.net/postgresqljson/how-to-load-json-postgresql/

https://konbert.com/blog/import-json-into-postgres-using-copy

在本博客中，我们将了解如何使用 PostgreSQL的COPY命令和名为jq的实用程序直接上传 JSON ！

jq链接：https://jqlang.github.io/jq/

如果您需要使用免费的 PostgreSQL 数据库，查看Aiven的免费计划！

https://go.aiven.io/francesco-signup

如果您需要优化 SQL 查询，请访问EverSQL：

https://www.eversql.com/?utm_medium=organic&utm_source=ext_blog&utm_content=ftisiotstackoverflow

PostgreSQL的COPY命令

首先，让我们检查一下 PostgreSQL的 COPY 命令。该命令允许您将文件中的数据复制到 PostgreSQL 表中，有两个版本：

1. COPY：文件已经位于 PG 服务器中

2. \COPY：文件位于通过以psql方式连接到服务器的客户端计算机中

在这两种情况下，标准COPY命令都具有以下最小参数集：

注释：

是目标表的名称

()定义表中要加载的列

指向要加载的源文件

定义数据的格式

完整的参数列表可在PostgreSQL COPY 文档中找到。COPY文档链接：

https://www.postgresql.org/docs/current/sql-copy.html

COPY命令开箱即用的格式

让我们重点关注PostgreSQL COPY 文档中列出的可用格式：

TEXT: 可用于加载全文

CSV：加载逗号（或其他分隔）值

BINARY：加载二进制文件

不幸的是，似乎没有现成的方法来加载 JSON 文件！

拯救者：COPY命令选项PROGRAM

一个可能不知道的COPY命令选项是指向要执行的程序而不是文件。

可以使用以下命令调用此选项：

与之前的\copy调用相比，这次我们添加了PROGRAM，一组由引号或双引号分隔的指令，这些指令将在加载数据之前在客户端计算机上执行。

如果您在服务器上使用COPY命令，您可能需要超级用户。这是Aiven中显示的错误消息：

ERROR: must be superuser or have privileges of the pg_read_server_files role to COPY from a file

因此，我们能做的就是在加载数据之前重塑数据。

jq—不可或缺的JSON解析工具

我在很多博客文章中使用 jq ，它是一个非常方便的解析、重塑、选择 JSON 文档的工具。出于本博客的目的，我们将使用将 JSON 输入重塑为 CSV 格式，以便通过PostgreSQL COPY命令进行加载。

您需要先在执行COPY命令的工作站上安装jq，可通过以下链接获取：

https://jqlang.github.io/jq/

让我们创建一个名为test.json基本 JSON 文件，内容如下：

{
    "id":1,
	"mystring":"ciao"
}
{
    "id":2,
	"mystring":"sole"
}
{
    "id":3,
	"mystring":"mare"
}

使用 jq，我们可以读取上面的 JSON 并将其重塑为 CSV 格式：

more test.json | jq -r ". | [.id, .mystring] | @csv"

在上面的命令中：

more test.json读取文件

jq -r打印原始输出

第一个.选择根级别的所有元素

| [.id, .mystring]从每个元素中检索id和键mystring

|@csv将输出格式设置为 CSV

输出是：

1,"ciao"
2,"sole"
3,"mare"

要检查 jq 可用的完整选项集，请查看手册 （https://jqlang.github.io/jq/manual/）。

jq应用转换JSON后加载到目标表

那么，我们如何仅用 1 个COPY命令加载目标表呢？

让我们首先创建目标表：

CREATE TABLE MYTARGETTABLE (id serial, myid int, mystring text);

现在，我们想用下面的COPY命令从test.json文件中加载到MYTARGETTABLE表的myid和mystring列，并使用jq应用转换。

\copy MYTARGETTABLE(myid, mystring) FROM PROGRAM 'more test.json | jq -r ". | [.id, .mystring] | @csv"' CSV

正确加载JSON数据到MYTARGETTABLE表后的输出：

 id | myid | mystring
----+------+----------
  1 |   1 | ciao
  2 |   2 | sole
  3 |   3 | mare
(3 rows)

从源头上解决问题有时有助于避免额外的跳跃! 将COPY(与PROGRAM)和jq拼接在一起，使我们能够灵活地加载JSON文件，而不需要中间表。