We learned in a previous tip how to export data into a file. Now let’s see how we can first convert the table data into JSON then export that!
Let’s use this data:
yugabyte=# SELECT * FROM one_big_row ORDER BY c1;
c1 | c2 | c3
----+----+------------
1 | A | 2022-04-03
2 | B | 2022-04-02
3 | C | 2022-04-01
(3 rows)
The first step is combining all of the table columns into a single row. We can do that by selecting the table from the table – I know, sounds weird, but it works.
Example:
yugabyte=# SELECT one_big_row FROM one_big_row ORDER BY c1;
one_big_row
------------------
(1,A,2022-04-03)
(2,B,2022-04-02)
(3,C,2022-04-01)
(3 rows)
Now we can add the TO_JSON function to convert each row into JSON:
yugabyte=# SELECT TO_JSON(one_big_row) json FROM one_big_row ORDER BY c1;
json
-------------------------------------
{"c1":1,"c2":"A","c3":"2022-04-03"}
{"c1":2,"c2":"B","c3":"2022-04-02"}
{"c1":3,"c2":"C","c3":"2022-04-01"}
(3 rows)
Finally we can export the JSON into a file:
yugabyte=# \! ysqlsh -AXtnqc "SELECT TO_JSON(one_big_row) json FROM one_big_row ORDER BY c1;" -o one_big_row.json
yugabyte=# \! cat one_big_row.json
{"c1":1,"c2":"A","c3":"2022-04-03"}
{"c1":2,"c2":"B","c3":"2022-04-02"}
{"c1":3,"c2":"C","c3":"2022-04-01"}
Have Fun!