CODEWITHSUNDEEP
sqlpostgresqljsonb
java_maestros
java_maestros

Reputation: 306

Creating NUMERIC array from JSONB data in Postgres

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.

Upvotes: 0

Views: 195

Answers (1)

Jim Jones
Jim Jones

Reputation: 19613

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)

Upvotes: 1

Related Questions