Postgres JSON Array data processing

Question

Have a table "json_test" and inserted the following record:

create table json_test ( v json);

insert into json_test values ('{"facilityId": ["20","30","40","50","51"]}')

SELECT trim(json_array_elements_text(v->'facilityId') ) from json_test

The above select lists the facility ID as individual rows.

I need the same rows in a Postgres function to insert the record into another table. I wrote the following code to return i. The output of the v_status when checked is (20,,,,,,,,,,,,). I need to get just 20, but I am unable to get that.

for i in SELECT json_array_elements_text(v->'facilityId') from json_test

loop

v_status:=  i;

end loop;

Dzmitry Savinkou · Accepted Answer · 2016-03-09 21:05:42Z

1

You have not specified entire function definition in your question. Assuming you have DDL:

CREATE TABLE json_test(
  id SERIAL PRIMARY KEY,
  v JSON
);

INSERT INTO json_test(v) VALUES
  ('{"facilityId": ["20","30","40","50","51"]}'::JSON);

You can check full PL/pgSQL guide as a reference, but your function may be defined as the following:

CREATE OR REPLACE FUNCTION get_facility_ids(rid INTEGER)
  RETURNS SETOF INTEGER AS $$
DECLARE
  t TEXT;
BEGIN
  FOR t IN SELECT json_array_elements_text(v->'facilityId')
    FROM json_test WHERE id = rid
  LOOP
    RETURN NEXT t;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT get_facility_ids(1) AS facultyId;

Just for your information, you can INSERT records from SELECT statements. Check the documentation.

answered Mar 9, 2016 at 21:05

Dzmitry Savinkou

5,2102 gold badges28 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres JSON Array data processing

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related