Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
select ('{98.016,1.3326125e+06,6.7176775e+06}'::real[])::float[];


float8


{98.01599884033203,1332612.5,6717677.5}



SELECT 1


select json_build_object('my_array',
                        ('{98.016,1.3326125e+06,6.7176775e+06}'::real[])::float[]);


json_build_object


{"my_array" : [98.01599884033203,1332612.5,6717677.5]}



SELECT 1


create table test (z,cityjsonfeature,translate,scale)as values
( 17
 ,'{"somekey":"someVal"}'::json
 ,'{98.016,1.3326125e+06,6.7176775e+06}'::real[]
 ,'{98.016,1.3326125e+06,6.7176775e+06}'::real[]);

SELECT 1


--here, Postgres handles the construction of the JSON
select 
CASE WHEN z > 16 
     THEN json_build_object( 'cityJson',cityjsonfeature
                            ,'metadata',json_build_object( 'translate',m.translate::float[]
                                                          ,'scale',m.scale)
                           )::text
     ELSE NULL 
END AS cityjson
from test m;


cityjson


{"cityJson" : {"somekey":"someVal"}, "metadata" : {"translate" : [98.01599884033203,1332612.5,6717677.5], "scale" : [98.016,1.3326125e+06,6.7176775e+06]}}



SELECT 1


--this is problematic
select 
CASE WHEN z > 16 THEN concat('{"cityJson" :', cityjsonfeature::TEXT,
                             ', "metadata" : {', 
                             '"translate:"','{', m.translate::float[]::text,'},',
                             '"scale:"','{', m.scale ,'}')
                 ELSE NULL 
END AS cityjson
from test m;


cityjson


{"cityJson" :{"somekey":"someVal"}, "metadata" : {"translate:"{{98.01599884033203,1332612.5,6717677.5}},"scale:"{{98.016,1.3326125e+06,6.7176775e+06}}



SELECT 1


--this is a middle ground
select 
CASE WHEN z > 16 
     THEN format('{"cityJson": %1$s, 
                   "metadata" : {"translate": [%2$s],
                                 "scale": [%3$s]
                                }
                  }',cityjsonfeature::TEXT
                    ,translate(m.translate::float[]::text,'{}','')
                    ,translate(m.scale::text,'{}',''))
     ELSE NULL 
END AS cityjson
from test m;


cityjson


{"cityJson": {"somekey":"someVal"}, 
                   "metadata" : {"translate": [98.01599884033203,1332612.5,6717677.5],
                                 "scale": [98.016,1.3326125e+06,6.7176775e+06]
                                }
                  }



SELECT 1


--if you like pretty formatting, you can also let postgres do it
select 
CASE WHEN z > 16 
     THEN jsonb_pretty(
             jsonb_build_object( 
                 'cityJson',cityjsonfeature
                ,'metadata',jsonb_build_object( 
                                'translate',m.translate::float[]
                               ,'scale',m.scale )
              )
          )
     ELSE NULL 
END AS cityjson
from test m;


cityjson


{
    "cityJson": {
        "somekey": "someVal"
    },
    "metadata": {
        "scale": [
            98.016,
            1332612.5,
            6717677.5
        ],
        "translate": [
            98.01599884033203,
            1332612.5,
            6717677.5
        ]
    }
}



SELECT 1