SQL直接查询出JSON格式的数据

1、用SQL查询出geojson格式的空间数据

-- 表:TABLEONE 数据库中的空间字段：geometry
SELECT row_to_json (fc) FROM
    (SELECT 'FeatureCollection' AS TYPE, array_to_json ( ARRAY_AGG (f)) AS Feature
    FROM 
        (SELECT 'Feature' AS TYPE, ST_AsGeoJSON ( '数据库中的空间字段geometry' ) :: json AS geometry, 
            (SELECT row_to_json ( T )
            FROM 
                (SELECT id,
						name,
						type,
						remark,
						status,
						...,
						time) AS T ) AS properties
                FROM TABLEONE
                WHERE 'geometry' IS NOT NULL
                ORDER BY  ID ASC ) AS f ) AS fc

2、用SQL查询普通表（不含有空间坐标等数据），结果为json格式

-- 表：TABLEONE
SELECT array_to_json(array_agg(row_to_json(t)))
	FROM 
    (SELECT *
			FROM TABLEONE) t; 

SELECT array_to_json(array_agg(row_to_json(t)))
	FROM 
    (SELECT id,name,type,remark,status,...,time
			FROM TABLEONE) t;