extracting ordered list of spatial points from JSON into PostgreSQL array

All we need is an easy explanation of the problem, so here it is.

i have JSONB data in PostgreSQL that looks like the following:

{"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
        }
      ]
    }
  }
]}

i’d like to convert it to as many arrays as there are "datasetGPolygonOuterGRing" objects that preserves the order of the points in each "gRingPoint". the above would provide two records:

[[[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]]]

i’ve tried several PostgreSQL JSON functions that extract all of the points into their own records which is clearly not desired.

Edit: i have figured out a solution as the following.

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

but would be interested in whether there is a "better" way to do this? would some implementation of lateral joins make it less verbose?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It seems it’s not necessary to group, as long as you unnest the inner array in a subquery…

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);

… or in a lateral join

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<>fiddle

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply