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}"

• joanolo: Which is the definition of the underying tables? How would you make a simple SELECT statement that would retrieve all the information you want in your JSON? And what's the JSON that you would like to get that "summarizes" the result of this SELECT?
• Jacob C.: I notice that my row_to_json will return multiple results. So I was just wondering if their is another way to obtain JSON format by using the zone table

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
 }
 }
 ]
}