CODEWITHSUNDEEP
jsonpostgresql
imatahi
imatahi

Reputation: 651

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

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.

Upvotes: 13

Views: 27642

Answers (2)

Daniel C. Moura
Daniel C. Moura

Reputation: 345

This is very easy to achieve with spyql:

$ psql -U my_user -h my_host -c "SELECT name AS player, score FROM my_table" --csv my_db | spyql "SELECT * FROM csv TO json"
{"player": "David", "score": 5.1}
{"player": "James", "score": 2.1}
{"player": "Joana", "score": null}
{"player": "Hellen", "score": 9.8}

We are using psql to export a query to CSV with the desired columns and then spyql to convert the CSV to json lines.

For the record, I created a sample table with the following SQL command:

CREATE TABLE my_table AS
SELECT *
FROM (VALUES (1, 'David', 5.1), (2, 'James', 2.1), (3, 'Joana', NULL), (4, 'Hellen', 9.8)) 
  AS t(id, name, score);

Disclaimer: I am the author of spyql

Upvotes: 2

Kristján
Kristján

Reputation: 18803

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.

Upvotes: 27

Related Questions