postgresql 将数据从 Postgres 导出到 json 并重命名飞列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31680518/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting data from Postgres to json and renaming the columns of the fly
提问by imatahi
How can I import only certain column from a table in json and rename them on the fly? That is:
如何仅从 json 表中导入某些列并动态重命名它们？那是：
MyTable (id, column1, column2, columns3)
And I want to export to json them as:
我想将它们导出为 json：
MyTable: {column11, column2, columns33}
So only 3 columns and 2 of them are renamed.
所以只有 3 列和 2 列被重命名。
回答by Kristján
Building on Export Postgres table as JSON, you can select the data you want from your table, convert it to JSON, and then copyit to a file. Here's a SQLFiddle showing the JSON conversion.
基于将 Postgres 表导出为 JSON，您可以从表中选择所需的数据，将其转换为 JSON，然后将copy其转换为文件。这是一个显示 JSON 转换的SQLFiddle。
Let's play with
让我们一起玩
CREATE TABLE data (id integer, name varchar(255), quantity integer);
INSERT INTO data VALUES
(1, 'apple', 10),
(2, 'banana', 20),
(3, 'cherry', 30)
;
First, get the data into the format you want, with fewer columns and any name changes.
首先，将数据转换为您想要的格式，减少列数并更改任何名称。
SELECT
name AS fruit_name,
quantity
FROM data;
Then, put this in a subquery and convert it to JSON.
然后，将其放入子查询并将其转换为 JSON。
SELECT row_to_json(fruit_data) FROM (
SELECT
name AS fruit_name,
quantity
FROM data
) fruit_data;
Finally, wrap everything in copy.
最后，将所有内容包装在copy.
COPY (
SELECT row_to_json(fruit_data) FROM (
SELECT
name AS fruit_name,
quantity
FROM data
) fruit_data
) TO 'a.file';
This will print each row as JSON line by line to the file
这会将每一行作为 JSON 逐行打印到文件中
{"fruit_name":"apple","quantity":10}
{"fruit_name":"banana","quantity":20}
{"fruit_name":"cherry","quantity":30}
Postgres can probably build these into an array before you output them, but I think it'd be simpler to postprocess the file into an array if that's the format you want.
Postgres 可能可以在您输出它们之前将它们构建到一个数组中，但我认为如果这是您想要的格式，将文件后处理为一个数组会更简单。
