Exporting data from Postgres to json and renaming the columns of the fly

Question

How can I import only certain column from a table in json and rename them on the fly? That is:

MyTable (id, column1, column2, columns3)

And I want to export to json them as:

MyTable: {column11, column2, columns33}

So only 3 columns and 2 of them are renamed.

Answer 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.

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.

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

{"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.