2

My table:

CREATE TABLE Points
(
    id integer NOT NULL DEFAULT nextval('point_id_seq'::regclass),
    info character varying(20) NOT NULL,
    my_point geometry(Point,4326),
    CONSTRAINT points_pkey PRIMARY KEY (id)
)

JSON text:

{
  "points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}
  ]
}

My function:

CREATE OR REPLACE FUNCTION add_multiple_geom ( multiple_geom json )
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$

DECLARE success numeric default 0;

BEGIN
    -- this insert only 1 row...
    INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point(lng,lat),4326), info;                                                              

  RETURN success;
END;

$BODY$;
  • I guess your question is really "how do I extract the long/lat pairs & info from JSON and iterate over them to insert?" – Philᵀᴹ Dec 29 '18 at 15:59
  • Hi Philᵀᴹ, yes, this was the final goal. Thanks for help :-) – two6nine1111 Dec 29 '18 at 23:04
1

You can unnest the array values using json_array_elements function and ->> operator:

create table t (data json);

insert into t 
values ('{"points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json);

select points->>'lat' as lat,
       points->>'lng' as lng,
       points->>'info' as info
from
     (select json_array_elements(data->'points') as points
      from t) p;
lat      | lng       | info
:------- | :-------- | :---
45.50884 | -73.58781 | x1;
41.56355 | -81.57327 | x2;
41.52335 | -81.57423 | x3;

db<>fiddle here

Now change your function by:

CREATE OR REPLACE FUNCTION add_multiple_geom (multiple_geom json)
    RETURNS numeric
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
AS $BODY$

DECLARE success numeric default 0;

BEGIN
    -- this insert only 1 row...
    INSERT INTO POINTS(my_point, info)
      SELECT ST_SetSRID(ST_Point((points->>'lat')::text::float8, 
                                 (points->>'lng')::text::float8), 
                                  4326), 
                        (points->>'info')::character varying
      FROM (SELECT json_array_elements(multiple_geom ->'points') AS points) p;

    RETURN success;
END;

$BODY$;

And try to use it:

DO $$
DECLARE data JSON;
BEGIN
    data := '{"points": [
    {"lat": 45.50884, "lng": -73.58781, "info": "x1"},
    {"lat": 41.56355, "lng": -81.57327, "info": "x2"},
    {"lat": 41.52335, "lng": -81.57423, "info": "x3"}]}'::json;

    PERFORM add_multiple_geom(data);
END $$;
SELECT * FROM Points;
id | info | my_point                                          
-: | :--- | :-------------------------------------------------
 1 | x1   | 0101000020E6100000C5724BAB21C14640C190D5AD9E6552C0
 2 | x2   | 0101000020E610000095D4096822C84440C971A774B06454C0
 3 | x3   | 0101000020E6100000492EFF21FDC2444074982F2FC06454C0

db<>fiddle here

  • Hi McNets, your solution was wonderful. Thanks for help :-) – two6nine1111 Dec 29 '18 at 23:00
  • I'm glad to help. – McNets Dec 29 '18 at 23:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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