主页 / dba / 问题 / 313415
Accepted
rocketboy2000
Asked: 2022-06-16 15:13:45 +0800 CST

从 JSON 中提取空间点的有序列表到 PostgreSQL 数组中

  • 772

我在 PostgreSQL 中有 JSONB 数据，如下所示：

{"datasetGPolygon": [
  {
    "datasetGPolygonOuterGRing": {
      "gRingPoint": [
        {
          "gRingLatitude": 49.922935,
          "gRingLongitude": -125.687365
        },
        {
          "gRingLatitude": 49.639177,
          "gRingLongitude": -126.126729
        },
        {
          "gRingLatitude": 49.382373,
          "gRingLongitude": -125.555556
        },
        {
          "gRingLatitude": 49.553726,
          "gRingLongitude": -125.02832
        },
        {
          "gRingLatitude": 49.95122,
          "gRingLongitude": -124.852575
        },
        {
          "gRingLatitude": 50.289339,
          "gRingLongitude": -125.204066
        },
        {
          "gRingLatitude": 50.261254,
          "gRingLongitude": -125.99492
        },
        {
          "gRingLatitude": 49.922935,
          "gRingLongitude": -125.687365
        }
      ]
    }
  },
  {
    "datasetGPolygonOuterGRing": {
      "gRingPoint": [
        {
          "gRingLatitude": 49.292889,
          "gRingLongitude": -124.843625
        },
        {
          "gRingLatitude": 49.60003,
          "gRingLongitude": -124.843625
        },
        {
          "gRingLatitude": 49.60003,
          "gRingLongitude": -124.409753
        },
        {
          "gRingLatitude": 49.292889,
          "gRingLongitude": -124.409753
        },
        {
          "gRingLatitude": 49.292889,
          "gRingLongitude": -124.843625
        }
      ]
    }
  }
]}

我想将其转换为与“datasetGPolygonOuterGRing”对象一样多的数组，这些对象保留每个“gRingPoint”中点的顺序。以上将提供两条记录：

[[[48.592142,-123.920288],[48.645205,-123.667603],[48.5262,-123.940887],[48.592142,-123.920288]],
[[48.592147,-123.920292],[48.645210,-123.667607],[48.5267,-123.940893],[48.592147,-123.920292]]]

我已经尝试了几个 PostgreSQL JSON 函数，这些函数将所有点提取到他们自己的记录中，这显然是不希望的。

编辑：我想出了一个解决方案如下。

with poly as (select poly.n n1, poly.points points from submission, jsonb_path_query(eml_json_source, '$.**.datasetGPolygon[*].datasetGPolygonOuterGRing.gRingPoint') with ordinality poly(points, n)),
points as (select poly.n1, arr.position n2, arr.point->>'gRingLatitude' lat, arr.point->>'gRingLongitude' long from poly,
jsonb_array_elements(points) with ordinality arr(point, position))
select array_agg('['||lat||','||long||']' order by n2) from points group by n1;

https://dbfiddle.uk/?rdbms=postgres_14&fiddle=93d068d74458abba95db424bc7e187cb

但会对是否有“更好”的方法来做到这一点感兴趣？横向连接的某些实现会使其不那么冗长吗？

postgresql spatial

1 个回答

  1. Best Answer

    似乎没有必要分组，只要您在子查询中取消嵌套内部数组...

    SELECT (
    SELECT array_agg('['||(arr.pt->>'gRingLatitude')||','||(arr.pt->>'gRingLongitude')||']' ORDER BY arr.n)
    FROM jsonb_array_elements(gp.poly->'datasetGPolygonOuterGRing'->'gRingPoint') WITH ORDINALITY arr(pt, n)
  ) arr
FROM submission s
CROSS JOIN jsonb_array_elements(s.eml_json_source->'datasetGPolygon') gp(poly);

    ...或在横向连接中

    SELECT v.arr
FROM submission s
CROSS JOIN jsonb_array_elements(s.eml_json_source->'datasetGPolygon') gp(poly)
CROSS JOIN LATERAL (
    SELECT array_agg('['||(arr.pt->>'gRingLatitude')||','||(arr.pt->>'gRingLongitude')||']' ORDER BY arr.n)
    FROM jsonb_array_elements(gp.poly->'datasetGPolygonOuterGRing'->'gRingPoint') WITH ORDINALITY arr(pt, n)
) v(arr);

    db<>小提琴

    • 0

相关问题