Exporting Postgres Rows as a JSON File

Question

I need to generate a JSON file for a dataTables section on my web site. The format of the JSON should look like this:

{
        "data": [
                [
                        "Tiger Nixon",
                        "System Architect",
                        "Edinburgh",
                        "5421",
                        "2011/04/25",
                        "$320,800",
                ],
                [
                        "Garrett Winters",
                        "Accountant",
                        "Tokyo",
                        "8422",
                        "2011/07/25",
                        "$170,750"
                ],
        ]
}

This format looks like an array of arrays listed on the JSON Creation Functions (Table 9-42.) -- array_to_json.

However, my data is not stored as arrays in my database. It's typical row data.

But when I try to export to JSON using "row_to_json" -- the format is way off from what I need. See this SQLfiddle example.

Basically, I'm trying to figure out the best approach to make the JSON export look more like the format pasted above.

Should I be reformatting my rows into arrays first, and storing them in a new table, so when I use "row_to_json" they come out as arrays? Or is there a better way?

klin · Accepted Answer · 2016-06-30 12:21:54Z

4

Select rows as arrays and use json_agg(array_to_json(...)):

select 
    json_build_object(
        'data', 
        json_agg(array_to_json(array[price::text, qty::text])))
from data;

                 json_build_object                  
----------------------------------------------------
 {"data" : [["11","11"], ["22","11"], ["33","11"]]}
(1 row)

You need a function introduced in Postgres 9.4, so SqlFiddle is useless.

edited Jun 30, 2016 at 12:21

answered Jun 30, 2016 at 1:19

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Clodoaldo Neto Over a year ago

json_build_object is from 9.4

Clodoaldo Neto Over a year ago

SQLFiddle is annoying anyway

Daniel C. Moura · Accepted Answer · 2021-12-21 19:39:36Z

You can use spyql for that:

$ psql -U my_user -h my_host -c "SELECT * FROM my_table" --csv my_db | spyql -Oindent=4 "SELECT list_agg(_values) AS data FROM csv TO json"
{
    "data": [
        [
            "1",
            "David",
            "5.1"
        ],
        [
            "2",
            "James",
            "2.1"
        ],
        [
            "3",
            "Joana",
            ""
        ],
        [
            "4",
            "Hellen",
            "9.8"
        ]
    ]
}

We are using psql to export a query/table to CSV and then spyql to convert the CSV to json. The _values variable in spyql contains a list with the value of each column before automatic type casting, therefore all values are strings. If you prefer having the casted values you can use the cols variable instead. list_agg aggregates all rows into a single array. Here's what you would get if used cols instead of _values (notice also the handling of NULL values):

$ psql -U my_user -h my_host -c "SELECT * FROM my_table" --csv my_db | spyql -Oindent=4 "SELECT list_agg(cols) AS data FROM csv TO json"
{
    "data": [
        [
            1,
            "David",
            5.1
        ],
        [
            2,
            "James",
            2.1
        ],
        [
            3,
            "Joana",
            null
        ],
        [
            4,
            "Hellen",
            9.8
        ]
    ]
}

For the record, I created the 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

Collectives™ on Stack Overflow

Exporting Postgres Rows as a JSON File

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related