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?

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

Answer 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