2

I have such PostGIS (PG:14-3.2) query today-:

SELECT
    p.id as "plant_id",
    p."location",
    p."name" as "plant_name",
    m."attributes_json",
    -- #placeholder,
    m."attributes_status",
    m."attributes_timestamp"
FROM metadata m
LEFT JOIN plant p
ON p.id = m.fk_plant_id
ORDER BY m.fk_plant_id ASC
;

The attributes_json is a GeoJSON feature containing multiple points.

I wish I could replace the #placeholder with a postgis MultiPoint as WKB or WKT which contains all the points of the GeoJSON field.

After some research, I end up there: Storing GeoJSON FeatureCollection to PostgreSQL with PostGIS? and when adapting the given example to my situation, I succeed retrieving a single MultiPoint WKT object, e.g.:

SELECT ST_AsText(ST_Collect(ST_GeomFromGeoJSON(feat->>'geometry')))
FROM (
  SELECT jsonb_array_elements(attribute_json::jsonb->'features') AS feat FROM metadata
) AS f;

But I'm not successful when it comes to inserting it at the place of the #placeholder in my initial query.

And if I replace the #placeholder by a one-shot statement:

    ST_Collect(ST_GeomFromGeoJSON((SELECT jsonb_array_elements(attributes_json::jsonb->'features'))->>'geometry')) as geom,

I have to add a GROUPY BY clause on almost every fields... and when it comes to adding the m.attributes_json in this clause, it says:

ERROR:  could not identify an equality operator for type json
LINE 18:  GROUP BY i.id, m.attributes_array, m.attribute_json
                                             ^

Any hints on how to achieve that in a proper and efficient way?

1 Answer 1

2

Either use

  • a correlated sub-query:
    SELECT
        p.id as "plant_id",
        p."location",
        p."name" as "plant_name",
        m."attributes_json",
        (
          SELECT
              ST_Collect(ST_GeomFromGeoJSON(f -> 'geometry'))
          FROM
              JSONB_ARRAY_ELEMENTS(m."attributes_json"::JSONB -> 'features') AS f
        ) AS geom,
        m."attributes_status",
        m."attributes_timestamp"
    FROM
        metadata m
    LEFT JOIN
        plant p ON p.id = m.fk_plant_id
    ORDER BY
        m.fk_plant_id ASC
    ;
    
  • a [LEFT JOIN | CROSS JOIN] LATERAL [ON TRUE]:
    SELECT
        p.id as "plant_id",
        p."location",
        p."name" as "plant_name",
        m."attributes_json",
        _m.geom,
        m."attributes_status",
        m."attributes_timestamp"
    FROM
        metadata m
    CROSS JOIN LATERAL (
      SELECT
          ST_Collect(ST_GeomFromGeoJSON(f -> 'geometry')) AS geom
      FROM
          JSONB_ARRAY_ELEMENTS(m."attributes_json"::JSONB -> 'features') AS f
    ) AS _m
    LEFT JOIN
        plant p ON p.id = m.fk_plant_id
    ORDER BY
        m.fk_plant_id ASC
    ;
    
    Note that with the CROSS JOIN LATERAL syntax rows are omitted when the LATERAL query returns no results (much like INNER JOIN behavior). To get a LEFT JOIN behavior, use LEFT JOIN LATERAL ( ... ) AS _m ON TRUE. However, this is mainly an issue for set-returning functions.

The LATERAL approach is more versatile (can return sets of rows), more elegant in set-returning scenarios, and, usually, much faster.

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.