postgresql查询返回jsonArray和geojson

品尝孤独

已于 2022-09-19 10:36:24 修改

阅读量1k

点赞数

文章标签： json

于 2022-06-09 15:47:36 首次发布

本文链接：https://blog.csdn.net/qq_39576977/article/details/125204806

版权

postgresql查询返回jsonArray

select array_to_json(array_agg(row_to_json(t.*))) as json_obj from cwcode.cwcode_platform t

select  row_to_json(t.*) from cwcode.cwcode_platform T limit 1

空间坐标转geojson

SELECT
	row_to_json (bb) 
FROM
	(
	SELECT
		
		'FeatureCollection' AS TYPE,
		array_to_json ( ARRAY_AGG ( aa ) ) AS features 
	FROM
		(
			SELECT
				'feature' AS TYPE,
				ST_AsGeoJSON ( positioning_coordinates ) :: json AS geometry,
				( SELECT row_to_json ( D ) FROM ( SELECT '要查询的字段，逗号分割' ) AS D ) AS properties 
				
			FROM
				dailymaintenance.maintenance_facility_record
			
		) AS aa
		
) AS bb