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

Question

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?

What data type is arrayelements? You state it's an array, but the data looks more like a JSON value. — user330315
– user330315, Commented Sep 7, 2022 at 6:11

ParmuTownley · Accepted Answer · 2022-09-07 07:04:05Z

1

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.

edited Sep 7, 2022 at 7:04

answered Sep 7, 2022 at 6:25

ParmuTownley

1,0072 gold badges19 silver badges39 bronze badges

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

Comments

Saya · Accepted Answer · 2022-09-07 06:13:32Z

0

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

select sum(cardinality(arrayElements)) from tableName;

answered Sep 7, 2022 at 6:13

Saya

3632 silver badges8 bronze badges

1 Comment

Saya Over a year ago

He said he wants to get count and my answer was that he can do it without unnesting the array.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related