Creating NUMERIC array from JSONB data in Postgres

Question

I have a Postgres JSONB data which contains an ARRAY of type NUMERIC. I want to extract this ARRAY and store it in a variable of type NUMERIC[]. Here's is my JSONB object.

{
  "userIds": [
    101,102,103
  ],
  "userRole": {
    "id": "1",
    "name": "Administrator"
  }
}

How can I extract userIds from this JSONB object and store them in NUMERIC[] as I have to iterate on this NUMERIC[]?

Any help would be highly appreciated.

Jim Jones · Accepted Answer · 2021-08-05 09:31:41Z

1

One way is to extract the ids with jsonb_array_elements, parse them to the right data type and aggregate them again in an array, e.g.:

SELECT array_agg(id) FROM (
  SELECT
    (jsonb_array_elements('{
      "userIds": [101,102,103],
      "userRole": {
         "id": "1",
         "name": "Administrator"
     }
   }'::jsonb->'userIds')::numeric)) j(id);

   array_agg   
---------------
 {101,102,103}
(1 row)

If you want to iterate over these values as rows in your resultset, don't bother with the outer query:

SELECT
  jsonb_array_elements('{
    "userIds": [101,102,103],
    "userRole": {
   "id": "1",
   "name": "Administrator"
    }}'::jsonb->'userIds')::numeric;

 jsonb_array_elements 
----------------------
                  101
                  102
                  103
(3 rows)

answered Aug 5, 2021 at 9:31

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

5 Comments

java_maestros Over a year ago

Jim Jones : What's wrong in this statement? Nothing is printing on console. FOREACH userId IN ARRAY(SELECT array_agg(id) FROM (SELECT json_array_elements(jsonb -> 'userIds') :: NUMERIC) j(id)) LOOP RAISE NOTICE 'userId --> %', userId; END LOOP;

Jim Jones Over a year ago

is the jsonb a variable/parameter?

java_maestros Over a year ago

yes it is a variable say userRoleJsonb which contains { "userIds": [ 101,102,103 ], "userRole": { "id": "1", "name": "Administrator" } }

Jim Jones Over a year ago

@java_maestros does it help ?

CREATE OR REPLACE FUNCTION testfunc(jsonb) RETURNS VOID LANGUAGE 'plpgsql' COST 100 VOLATILE AS $BODY$ DECLARE i record; BEGIN   FOR i IN SELECT * FROM jsonb_array_elements($1->'userIds') j(id) LOOP     RAISE NOTICE 'output from space %', i.id;   END LOOP; END; $BODY$;

java_maestros Over a year ago

thanks it worked like a charm. I was using the wrong function. It's jsonb_array_elements.

Collectives™ on Stack Overflow

Creating NUMERIC array from JSONB data in Postgres

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related