主页 / dba / 问题 / 90482
Accepted
AliBZ
Asked: 2015-01-29 15:22:28 +0800 CST

将 Postgres 表导出为 json

  • 772

有没有办法将 postgres 表数据作为 json 导出到文件中？我需要逐行输出，例如：

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

编辑：postgres 版本：9.3.4

postgresql postgresql-9.3

8 个回答

  1. Best Answer

    在这里尝试了解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

    然后我发出了这个命令

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

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

    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]$

    （您可以尝试从

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

    闲暇时）。

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

    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

    给出：

    [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]$

    最后，@ AdamGent\在他的帖子中提到了反斜杠 ( ) 问题。这有点棘手，但无需借助查询后处理即可。瞧：

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

    并因此使用 REGEXP_REPLACE（注意演员表 ::TEXT）删除了多余的黑斜线。

    test=# \o slash.json
test=# SELECT REGEXP_REPLACE(ROW_TO_JSON(t)::TEXT, '\\\\', '\\', 'g') 
test=# FROM (SELECT * FROM fred) AS t;  -- I found that using a CTE was helpful for legibility
test=# \o
test=# \q

    给出：

    [pol@polhost inst]$ more slash.json 
                    regexp_replace                    
------------------------------------------------------
 {"mary":2,"jimmy":43,"paulie":"asfasfasfd"}
 {"mary":3,"jimmy":435,"paulie":"ererere"}
 {"mary":6,"jimmy":43343,"paulie":"eresdfssfsfasfae"}
 {"mary":35,"jimmy":5,"paulie":"wrew\sdfsd"}
 {"mary":3,"jimmy":44545,"paulie":"\sdfs\\\sfs\\gf"}
(5 rows)
[pol@polhost inst]$

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

    • 70

  2. 如果您正在使用，psql那么根本没有理由使用\COPY。

    \t
\a
\o file.json
SELECT row_to_json(r) FROM my_table AS r;

    这与我们使用 PostGIS 从数据库中获取 png/jpgs/tifs 以进行快速测试以及生成具有 PostgreSQL 扩展名的脚本文件的方法相同。

    • 34

  3. 请在下面找到输出实际有效 JSON（即对象数组）的唯一答案。

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

    （来源）

    • 17

  4. 对我来说，@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';
    • 9

  5. 我将在Verace 的回答中添加一个特别警告。如果您有带有反斜杠字符的文本列，则需要对输出的 JSON 文件进行后处理：\ .

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

    这个：

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

    变成

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

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

    您可以将\\into替换为\sed：

    sed -i -e 's/\\\\/\\/g' PG_OUT_JSON_FILE.json

    从他们提到的Postgres COPY中：

    目前，COPY TO 永远不会发出八进制或十六进制数字反斜杠序列，但它确实使用上面列出的其他序列来处理这些控制字符。上表中未提及的任何其他反斜杠字符都将被视为代表自身。但是，请注意不要添加不必要的反斜杠，因为这可能会意外生成与数据结束标记 (.) 或空字符串（默认为 \N）匹配的字符串。这些字符串将在任何其他反斜杠处理完成之前被识别。

    强烈建议生成 COPY 数据的应用程序将数据换行符和回车分别转换为 \n 和 \r 序列。目前可以用反斜杠和回车来表示数据回车，用反斜杠和换行来表示数据换行。但是，这些表示形式可能不会在未来的版本中被接受。如果 COPY 文件在不同的机器之间传输（例如，从 Unix 到 Windows，反之亦然），它们也极易受到损坏。

    COPY TO 将使用 Unix 样式的换行符 ("\n") 终止每一行。在 Microsoft Windows 上运行的服务器改为输出回车/换行符 ("\r\n")，但仅用于 COPY 到服务器文件；为了跨平台的一致性，无论服务器平台如何，COPY TO STDOUT 总是发送“\n”。COPY FROM 可以处理以换行符、回车符或回车符/换行符结尾的行。为了降低由于作为数据的非反斜杠换行符或回车而导致的错误风险，如果输入中的行结尾不完全相同，COPY FROM 会报错。

    • 8

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

    完全披露：我编写了那个软件。

    • 1

  7. 使用spyql很容易实现：

    $ psql -U my_user -h my_host -c "SELECT * FROM my_users" --csv my_db | spyql "SELECT * FROM csv TO json"
{"id": 1, "name": "David"}
{"id": 2, "name": "James"}
{"id": 3, "name": "Joana"}
{"id": 4, "name": "Hellen"}

    我们使用 psql 将查询/表导出为 CSV，然后使用 spyql 将 CSV 转换为 json 行。

    作为记录，我使用以下 SQL 命令创建了一个示例表：

    CREATE TABLE my_users AS
SELECT *
FROM (VALUES (1, 'David'), (2, 'James'), (3, 'Joana'), (4, 'Hellen')) AS t(id, name);

    免责声明：我是spyql的作者

    • 1

  8. @gunar-gessner 答案的变体，但带有内联指定的输出文件

    \t
\a
select json_agg(t) FROM (SELECT * from table) t \g data.json
    • 0

相关问题