Postgres SQL output JSON Error

Question

I'm trying to create a SQL code in which I want to return JSON similar to this format:

{
  "type": "FeatureCollection",
  "features": [
   {
     "type": "Feature",
      "properties": {
      "id": 1,
      "zone": "A",
      "nb_stats": 136,
      "occupation": 0
   },...]}

So far I manage to code a script that output a certain part of the JSON format, but I'm enable to generate the properties field with the values.

Here my SQL code:

Select json_build_object(
'type', 'FeatureCollection',
'features',json_build_array(
  json_build_object(
  'type','Feature',
'properties', (select row_to_json(properties) from (Select id, zone, nb_stats,occupation from public.zone) properties))))

By executing this code, I'm always getting a error saying that the subQuery return more than line. Error 21000.

Please note that if I only execute the select row_to_json(properties) I am able to output the result all the values of the table:

select row_to_json(properties) from (Select id, zone, nb_stats,occupation from public.zone) properties

"{"id":1,"zone":"A","nb_stats":136,"occupation":0}"

Answer 1

You can use a PostgreSQL Aggregate Function to form a JSON array from the set of subquery results.

Aggregate functions compute a single result from a set of input values.

Also, the function json_build_object expects key value pairs, but you can deal with that by expressly providing the column name as a string for the key.

For example, given this setup:

CREATE TABLE zone (
    id serial PRIMARY KEY,
    name varchar(150) NOT NULL,
    nb_stats integer NOT NULL,
    occupation integer NOT NULL
);

INSERT INTO zone VALUES (1, 'a', 136, 0);
INSERT INTO zone VALUES (2, 'b', 145, 1);
INSERT INTO zone VALUES (3, 'c', 120, 0);

And this query:

select json_build_object(
    'type', 'FeatureCollection', 'features',
    (select json_agg(p1) from (select 'feature' as type,
        json_build_object('id', id, 'name', name, 'nb_stats', nb_stats,
            'occupation', occupation) as properties
        from zone) p1));

You get this result:

=# \i query.sql
                                                              json_build_object
---------------------------------------------------------------------------------------------------------------------------------------------
 {"type" : "FeatureCollection", "features" : [{"type":"feature","properties":{"id" : 1, "name" : "a", "nb_stats" : 136, "occupation" : 0}}, +
  {"type":"feature","properties":{"id" : 2, "name" : "b", "nb_stats" : 145, "occupation" : 1}},                                             +
  {"type":"feature","properties":{"id" : 3, "name" : "c", "nb_stats" : 120, "occupation" : 0}}]}

Rows are joined by newlines. If you need to get rid of the newlines, you can use the regexp_replace() function with the pattern E'\n' and the 'g' global modifier. But json is an actual type in PostgreSQL, and regexp_replace() expects text type input. So to use that function you'll need to cast the json to text, then cast the newline-free resulting text back to json, like so:

select json_build_object(
    'type', 'FeatureCollection', 'features',
    (select regexp_replace(json_agg(p1)::text, E'\n', '', 'g')::json from (select 'feature' as type,
        json_build_object('id', id, 'name', name, 'nb_stats', nb_stats,
            'occupation', occupation) as properties
        from zone) p1));

Alternatively, you can solve the problem programmatically by writing a script that connects to the database, selects rows from the zone table, and then marshals them into JSON.

For example, here's a simple Go script that does that:

package main

import(
    "database/sql"
    "encoding/json"
    "fmt"
    _ "github.com/lib/pq"
)

type ZoneRow struct {
    Id          int
    Name        string
    NbStats     int
    Occupation  int
}

type Feature struct {
    Type        string
    Properties  ZoneRow
}

type Result struct {
    Type        string
    Features    []Feature
}

func (res *Result) AddZoneRow(id int, name string, nbStats int, occupation int) {
    newZoneRow := Feature{`Feature`, ZoneRow{id, name, nbStats, occupation}}
    res.Features = append(res.Features, newZoneRow)
}

func main() {
    db, err := sql.Open(`postgres`,
        `host=/path/to/postgresql/ user=me dbname=mine`)
    if err != nil {
        fmt.Println(err)
    } else {
        rows, err := db.Query(`SELECT * FROM zone`)
        defer rows.Close()
        if err != nil {
            fmt.Println(err)
        } else {
            res := new(Result)
            res.Type = `FeatureCollection`
            for rows.Next() {
                var id int
                var name string
                var nbStats int
                var occupation int
                err = rows.Scan(&id, &name, &nbStats, &occupation)
                if err != nil {
                    fmt.Println(err)
                } else {
                    res.AddZoneRow(id, name, nbStats, occupation)
                }
            }
            err = rows.Err()
            if err != nil {
                fmt.Println(err)
            } else {
                empty := ``
                fourSpaces := `    `
                b, err := json.MarshalIndent(res,empty,fourSpaces)
                if err != nil {
                    fmt.Println(err)
                } else {
                    fmt.Println(string(b))
                }
            }
        }
    }
}

Which, given the same table and row setup above, prints this output:

{
    "Type": "FeatureCollection",
    "Features": [
        {
            "Type": "Feature",
            "Properties": {
                "Id": 1,
                "Name": "a",
                "NbStats": 136,
                "Occupation": 0
            }
        },
        {
            "Type": "Feature",
            "Properties": {
                "Id": 2,
                "Name": "b",
                "NbStats": 145,
                "Occupation": 1
            }
        },
        {
            "Type": "Feature",
            "Properties": {
                "Id": 3,
                "Name": "c",
                "NbStats": 120,
                "Occupation": 0
            }
        }
    ]
}