2

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}"
  • 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? – joanolo Apr 1 '17 at 14:24
  • 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 – Jacob C. Apr 1 '17 at 18:23
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
            }
        }
    ]
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.