1

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?

2
  • Valid JSON only has distinct key names, you cannot have duplicate datasetGPolygonOuterGRing keys dbfiddle.uk/…. What does your JSON really look like? 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. Commented Jun 17, 2022 at 17:39

1 Answer 1

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.