Question 114779 in PostgreSQL Question posted in
The official documentation can be found here.

how to split rows into multiple rows based on values in an array column in postgresql? (see example in description)

i a have postgresql query that gets results like this:

id | arrayElements
 0 | [0, 2, 1]
 1 | [0, 3]
 2 | [1]

In the above example the values inside arrayElements means something. I want to split this table based on those arrayElements (to get count).

I want to convert it to:

id | arrayElement
 0 | 0
 0 | 2
 0 | 1
 1 | 0
 1 | 3
 2 | 1

here i’m essentially spliting rows by elements inside arrayElements column. arrayElements column contains string data.

i am using postgresql 14.

my end goal is to get the count per arrayElement which is why i’m separating them into different rows.

how should I solve this?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0 
    select id, UNNEST(string_to_array(arrayElements, ',') as arrayElement;

    should do it. In my case arrayElement was a string that I had to convert to an array.

    There may be the case that elements might have a '[' or ']' at the beginning or end so to fix that you can modify the query to use SUBSTRING and remove those square brackets:

    SELECT
  SUBSTRING(UNNEST(string_to_array(arrayElements, ',')), '([0-9]{1,10})') AS "arrayElement",
  COUNT(id)
FROM table
GROUP BY arrayElement

    the regex used in substring assumes that the maximum number of digits arrayElement can have is 10.


  2. 0

    Also, if you want to get count of arrayElements, you can do this:

    select sum(cardinality(arrayElements)) from tableName;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search