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}"
Best Answer
You can use a PostgreSQL Aggregate Function to form a JSON array from the set of subquery results.
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:
And this query:
You get this result:
Rows are joined by newlines. If you need to get rid of the newlines, you can use the
regexp_replace()
function with the patternE'\n'
and the'g'
global modifier. Butjson
is an actual type in PostgreSQL, andregexp_replace()
expectstext
type input. So to use that function you'll need to cast thejson
totext
, then cast the newline-free resultingtext
back tojson
, like so: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:
Which, given the same table and row setup above, prints this output: