将Postgres表导出为json

有没有一种方法可以将Postgres表数据作为json导出到文件中？我需要逐行输出，例如：

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

编辑：postgres版本：9.3.4

#1 楼

在这里尝试获取PostgreSQL和JSON的基本介绍。

此外，PostgreSQL文档非常好，因此请在此处尝试。请检查pretty_bool选项。

您最初的问题是“是否可以将postgres表数据导出为JSON”。您想要这种格式的文件

{'id':1,'name':'David'}
{'id':2,'name':'James'}
...

我没有PostgreSQL的运行实例，因此我下载，编译并安装了9.4。

要回答这个问题，我首先CREATE桌子（fred）

CREATE TABLE fred (mary INT, jimmy INT, paulie VARCHAR(20));

INSERT INTO fred VALUES (2,    43, 'asfasfasfd'      );
INSERT INTO fred VALUES (3,   435, 'ererere'         );
INSERT INTO fred VALUES (6, 43343, 'eresdfssfsfasfae');

然后检查： >

test=# select * from fred;

 mary | jimmy |      paulie      
------+-------+------------------
    2 |    43 | asfasfasfd
    3 |   435 | ererere
    6 | 43343 | eresdfssfsfasfae

然后我发出此命令

然后我退出psql并列出文件myfile。

test=# COPY (SELECT ROW_TO_JSON(t) 
test(# FROM (SELECT * FROM fred) t) 
test-# TO '/paulstuff/sware/db/postgres/inst/myfile';
COPY 3
test=#

（可以随意使用

test=# \q
[pol@polhost inst]$ more myfile 
{"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
{"mary":3,"jimmy":435,"paulie":"ererere"}
{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
[pol@polhost inst]$

的输出进行试验）。

通过@ offby1确认输出（虽然对应于OP的问题）不正确JSON。 @EvanCarroll指出，\o也是一种输出到文件的方式，因此我在此语句中结合了这两个小问题的解决方案（在这里提供了帮助）：

COPY (SELECT ROW_TO_JSON(t, TRUE)  -- <-- Note addition of "TRUE" here!

test=# \o out.json
test=# SELECT array_to_json(array_agg(fred), FALSE) AS ok_json FROM fred;
                                     -- <-- "TRUE" here will produce plus
                                        ("+) signs in the output. "FALSE"
                                        is the default anyway.
test=# \o

最后，@ AdamGent在他的帖子中提到了反斜杠（\）问题。这有点棘手，但是有可能不诉诸查询后处理。

[pol@polhost inst]$ more out.json 
                                                                   ok_json                                                                    
----------------------------------------------------------------------------------------------------------------------------------------------
 [{"mary":2,"jimmy":43,"paulie":"asfasfasfd"},{"mary":3,"jimmy":435,"paulie":"ererere"},{"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}]
(1 row)
[pol@polhost inst]$

并使用REGEXP_REPLACE从而（请注意演员：: TEXT）去除了多余的黑杠。 br />给出：

INSERT INTO fred VALUES (35, 5, 'wrew\sdfsd');
INSERT INTO fred VALUES (3, 44545, '\sdfs\\sfs\gf');

（ps至于@Zoltán的评论-这可能是版本的东西-无法复制！）。

这似乎正是原始海报想要的。但是请注意，尽管每行都是正确的JSON，但行的集合却不是，因为它缺少逗号分隔行和包围行的方括号。

–offby1
16年4月30日在16:44

如果您的列中有任何反斜杠，这将不起作用！！！请仔细阅读COPY文档，因为它会对反斜杠字符进行特殊处理（例如添加另一个反斜杠）。

–亚当·根特（Adam Gent）
16年8月25日在2:37

阅读下面的@AdamGent答案以解决反斜杠问题

– FacePalm
17年2月8日在7:29

所以... 2017年，没有办法使用COPY命令PostgreSQL导出JSON？有CSV选项，TXT选项...为什么没有JSON选项？

– Peter Krauss
17年11月5日，11：50

谢谢@Vérace。抱歉，现在我使用复杂的JSONb测试了COPY，并且处理的JSON很好，“正确的JSON”！

– Peter Krauss
17年11月5日，12：15

#2 楼

如果您使用的是psql，则根本没有理由使用\COPY。使用PostGIS对数据库进行快速测试，并生成具有PostgreSQL扩展名的脚本文件。

大！像通常的COPY命令“不允许相对路径”一样，psql-native-commands是复制到相对路径的最简单方法！ PS：有一种“终端方式”可以将真实的COPY命令与相对路径一起使用，请参见此处。 psql -h remotehost -d remote_mydb -U myuser -c“ COPY（SELECT'{\” x \“：1，\” y \“：[\” a \“，2]}':: json AS r）TO STDOUT”> ./relative_path/file.csv

– Peter Krauss
17年11月5日在12:28

请注意，文件file.json中的输出不是JSON，而是该文件中的每一行都是代表一行的JSON对象。但是该文件完全不是有效的JSON，您需要将整个文件包装在[和]之间，并在行末添加所需的逗号。

– Flimm
20 Sep 25 '17：01

#3 楼

对我来说，@Vérace的答案没有保留列名，而是分配了默认名称（f1，f2等）。我正在使用带有JSON扩展名的PostgreSQL 9.1。

如果要导出整个表，则不需要子查询。此外，这将保留列名。我使用了以下查询：

COPY (SELECT row_to_json(t) FROM fred as t) to '/home/pol/Downloads/software/postgres/inst/myfile';

它确实维护了列名！创建表fred（mary INT，jimmy INT，paulie VARCHAR（20））;结果为：{“ mary”：2，“ jimmy”：43，“ paulie”：“ asfasfasfd”}-字段名称为mary，jimmy，paulie ...和NOT（f1，f2等）...

–Vérace
17年11月30日在20:58

#4 楼

我将对Verace的回答特别说明。如果您的文本列带有反斜杠字符，则需要对输出的JSON文件进行后处理：\。

否则，您最多会得到重复（\-> \），而更糟的是会完全无效的JSON，即： />
成为

{ "f1" : "crap\""}.

看起来不错但完全无效的JSON。

可以用sed将\替换为\ ：

{ "f1" : "crap\""}.

从Postgres COPY那里绕过它提及：十六进制数字反斜杠
序列，但是它确实将上面列出的其他序列用于那些
控制字符。上表中未提及的其他任何反斜杠字符都将被用来表示自己。
但是，请注意不要添加反斜杠，因为这可能会意外地产生与末尾匹配的字符串，数据标记（。）或
空字符串（默认为\ N）。这些字符串将在任何其他反斜杠处理之前被识别。

强烈建议生成COPY数据的应用程序
将数据换行，并将回车符返回到\ n和\ r个序列
。目前，可以用反斜杠和回车来表示数据回车，也可以用反斜杠和换行来表示数据新行。但是，将来的发行版中可能不接受这些表示
。如果COPY文件在不同的计算机上传输（例如，从Unix到Windows，反之亦然），它们也很容易受到破坏。

COPY TO将终止每个行以Unix样式的换行符（“ \ n”）。
在Microsoft Windows上运行的服务器改为输出回车
返回/换行符（“ \ r \ n”），但仅用于COPY到服务器文件；为了实现跨平台的一致性，无论服务器平台如何，COPY TO STDOUT始终发送“ \ n”
。 COPY FROM可以处理以
换行符，回车符或回车符/换行符结尾的行。为了减少
由于反斜杠换行或将换行符（
）用作数据而导致的错误风险，如果
输入中的行尾并不相同，则COPY FROM会抱怨。 br />

我已经在回答中解决了这一问题-希望您觉得满意。如果没有，请告诉我。

–Vérace
19年9月9日13:58

#5 楼

这是输出有效JSON（对象数组）的唯一方法。

\t
\a
\o data.json
select json_agg(t) FROM (SELECT * from table) t;

（源）

#6 楼

有关无需安装任何软件（Docker除外）的通用（MySQL，Postgres，SQLite ..）和免费解决方案，请参见https://github.com/function61/sql2json

完全公开：我写了那个软件。

编程黑洞网