将数据从Postgres导出到json并重命名fly的列

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列。

1 个解决方案

#1

Building on Export Postgres table as JSON, you can select the data you want from your table, convert it to JSON, and then copy it to a file. Here's a SQLFiddle showing the JSON conversion.

在Export Postgres表的基础上构建为JSON，您可以从表中选择所需的数据，将其转换为JSON，然后将其复制到文件中。这是一个显示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 (
  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可能会在输出之前将它们构建成一个数组，但我认为如果这是你想要的格式，将文件后处理成一个数组会更简单。

#1