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.
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.
SELECT row_to_json(fruit_data) FROM (<the data query>) fruit_data; - what's fruit_data?<the data query>?<the JSON query>? Could you be more specific?
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
There's another function called json_object_agg, which can generate an object instead of an array.
It can be used like this:
select json_object_agg(your_table.id, your_table) from your_table;
Output like this:
{ "1" : {"id":1,"name":"abc","age":18,"sex":"m"}, "2" : {"id":2,"name":"abc","age":18,"sex":"f"}, "4" : {"id":4,"name":"abc","age":18,"sex":"f"} }
Related functions include: json_agg, jsonb_agg, json_object_agg, jsonb_object_agg. (There is also xmlagg; for more, you can check this doc). One of their differences is that the jsonb versions will remove duplicate keys, while the json versions will retain all content even if the keys are duplicated.
