SQL切换多边形字符串中的纬度和经度位置

Question

我有一个包含多边形信息的文本

示例数据:

{"points":[{"lat":49.454912346,"lng":-123.1000021159},{"lat":47.454912346,"lng":-122.1000021159},{"lat":55.454912346,"lng":-121.1000021159},{"lat":52.454912346,"lng":-127.1000021159},{"lat":26.454912346,"lng":-129.1000021159}]

如果有任何方法可以交换"lat"one_answers";lng"在这个字符串在SQL服务器?

预期结果:

{"points":[{"lng":-123.1000021159,"lat":49.454912346}

任何帮助感谢!

Answer 1

这里是JSON。您可以使用OPENJSON拆分信息，将其交换，然后使用FOR JSON重新构建它:

DECLARE @json nvarchar(max) = '{"points":[{"lat":49.454912346,"lng":-123.1000021159},{"lat":47.454912346,"lng":-122.1000021159},{"lat":55.454912346,"lng":-121.1000021159},{"lat":52.454912346,"lng":-127.1000021159},{"lat":26.454912346,"lng":-129.1000021159}]'
SELECT j.lng, j.lat
FROM OPENJSON(@json, '$.points')
WITH (lat decimal(18,10), lng decimal(18,10)) j
FOR JSON PATH, ROOT('points');

,db&lt的在小提琴

如果你有一个表，你想从中选择，你可以这样做

SELECT
(
SELECT
j.lng, j.lat
FROM OPENJSON(t.json, '$.points')
WITH (lat decimal(18,10), lng decimal(18,10)) j
FOR JSON PATH, ROOT('points')
) AS json
FROM YourTable t;