extracting ordered list of spatial points from JSON into PostgreSQL array

Question

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?

Valid JSON only has distinct key names, you cannot have duplicate datasetGPolygonOuterGRing keys dbfiddle.uk/…. What does your JSON really look like? — Charlieface
– Charlieface, Commented Jun 17, 2022 at 8:56
sorry, i've adjusted the example to make it obvious that we are dealing with an array of datasetGPolygonOuterGRing objects. — rocketboy2000
– rocketboy2000, Commented Jun 17, 2022 at 17:39

Charlieface · Accepted Answer · 2022-06-18 23:11:48Z

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

Stack Exchange Network

extracting ordered list of spatial points from JSON into PostgreSQL array

1 Answer 1

Hot Network Questions

extracting ordered list of spatial points from JSON into PostgreSQL array

1 Answer 1

Related

Hot Network Questions