Filter 2-dimensional array

Question

I have this array:

1:0, 2:0, 3:0, 4:0, 5:0, 6:0, 7:0, 8:0, 9:0, 10:0,11:0,12:0,13:0,14:0,15:0,16:0
17:0,18:0,19:0,20:0,21:0,22:0,23:0,24:0,25:0,26:0,27:0,28:0,29:0,30:0,31:0,32:0,
49:0,33:0,34:0,35:0,36:0,37:0,38:0,39:0,40:0,41:0,42:0,43:0,44:0,45:0,46:0,47:0,
48:0,50:0,51:0,52:0,53:0,54:0,55:0,56:0,57:0,58:0,59:0,60:0,61:0,62:0,63:9,64:0,
65:0,66:0,67:0,68:0,69:0,70:0,71:0,72:0,73:0,74:0,75:0,76:0,77:0,78:0,79:0,80:0,
81:0,82:0,83:0,84:0,85:0,86:0,87:0,88:0,89:0,90:0,91:0,92:0,93:0,94:0,95:0,96:0,
97:0,98:0,99:0,100:0

I want to filter all entry like *:0 so that I only get this result:

63:9

I think I have to describe it better:

I have a table users with a field user_skill.
In this field is a such a string: 1:0, 2:0, 3:0, 4:3, 5:8, 6:9, 7:0, 8:0, 9:0 with this syntax: skill_id:prio,skill_id:prio,skill_id:prio,skill_id:prio,...

Now I want to to join the users table with the skills table like this:

SELECT skill_name
FROM users
inner join skills on skills.skill_id = ANY (string_to_array(regexp_replace(user_skill,':[0-9]*','','g'),',')::int[])
where user_id = 16
order by skill_name

That works well but I only want to see skill_name where the user has prio <> 0.

How about loop over the array and pick up the elements you're interested in? — Aleks G, Jun 26 '13 at 11:50
First you call it an `array`, then you call it a `string`. Example uses a string ... The table definition and your PostgreSQL version from `SELECT version()` would help. A `,` is missing in your example data and are there really spaces in the string? — Erwin Brandstetter, Jun 26 '13 at 20:31
Internally PostgreSQL has an array map function. It executes function and args across each no null array element. I can't see a way its exposed via an SQL call to do this. — Tim Child, Jun 27 '13 at 12:43
@TimChild: [PostgreSQL has an intrinsic array data type](http://www.postgresql.org/docs/current/static/arrays.html). Also an hstore contrib module, which is basically key/value storage. — Mike Sherrill 'Cat Recall', Jul 12 '13 at 13:41

score 1 · Answer 1 · edited Jun 20 '20 at 09:12

Proper solution

You might want to familiarize yourself with normalization and implement this as a proper n:m relation between the tables users and skills with an additional attribute prio in the user_skill table. Here is a complete recipe:
How to implement a many-to-many relationship in PostgreSQL?

Then your query can be very simple:

SELECT s.skill_name
FROM   user_skill uk
JOIN   skills s USING (skill_id)
WHERE  uk.user_id = 16
AND    uk.prio <> 0
ORDER  BY s.skill_name;

It can (and should) be backed up with indices and will be faster by several orders of magnitude than what you have right now.
It will need some more space on disk.

Solution for the dark side

While being locked in this unfortunate situation you can help yourself with this query. However, this assumes at least Postgres version

SELECT s.skill_name
FROM  (
   SELECT split_part(us_item, ':', 1) AS skill_id
   FROM  (
      SELECT trim(unnest(string_to_array(user_skill, ','))) AS us_item
      FROM   users
      WHERE  user_id = 16      -- enter user_id here
      ) x
   WHERE  split_part(us_item, ':', 2) <> '0'
   ) u
JOIN   skills s USING (skill_id)
ORDER  BY 1;

Demo with example:

SELECT split_part(us_item, ':', 1) AS skill_id
FROM  (
   SELECT  trim(unnest(string_to_array(
'1:0, 2:0, 3:0, 4:0, 5:0, 6:0, 7:0, 8:0, 9:0, 10:0,11:0,12:0,13:0,14:0,15:0,16:0,'
'17:0,18:0,19:0,20:0,21:0,22:0,23:0,24:0,25:0,26:0,27:0,28:0,29:0,30:0,31:0,32:0,'
'49:0,33:0,34:0,35:0,36:0,37:0,38:0,39:0,40:0,41:0,42:0,43:0,44:0,45:0,46:0,47:0,'
'48:0,50:0,51:0,52:0,53:0,54:0,55:0,56:0,57:0,58:0,59:0,60:0,61:0,62:0,63:9,64:0,'
'65:0,66:0,67:0,68:0,69:0,70:0,71:0,72:0,73:0,74:0,75:0,76:0,77:0,78:0,79:0,80:0,'
'81:0,82:0,83:0,84:0,85:0,86:0,87:0,88:0,89:0,90:0,91:0,92:0,93:0,94:0,95:0,96:0,'
'97:0,98:0,99:0,100:0', ','))) AS item
   ) x
WHERE  split_part(us_item, ':', 2) <> '0';

trim() deals with leading and trailing spaces, like you have in your example. But those may just be artifacts in the sloppy question.

I fixed a missing ,.
BTW, the SQL standard allows to enter string literal like I demonstrate. Weird, but sometimes useful.

i understand, but im not able to create a skill table, i only have read rights :( — marvin, Jun 26 '13 at 14:00
Interesting... As I read his question, I always assumed his array was stored as such, and that he was indicating the subscripts. @marvin: are you actually storing the skills and their levels in a text field that you need to parse on in addition to query against? :-| — Denis de Bernardy, Jun 26 '13 at 20:43

Filter 2-dimensional array

1 Answers1

Proper solution

Solution for the dark side