How to select array of Integers in postgresql

Question

I want to select from the table arrays of integers like this:

[1, 2, 3]

Now in trying something like this:

(SELECT array_to_json(array_agg(row_to_json(s))) FROM(
 SELECT specialty FROM talent_specialty WHERE userid = 840 )s);

and this is the record that query is returning

[{"specialty":1},{"specialty":2}]

Table look like this:

So just to make sure i understood. You have [{"specialty":1},{"specialty":2}, ...] and want [1,2,3,...]? — Tony
– Tony, Commented Jan 16, 2019 at 15:05
[{"specialty":1},{"specialty":2}, ...] is the value from your table? Or does your table contain columns with rows 1,2,3? It would be nice if you could add a sample table to your question — S-Man
– S-Man, Commented Jan 16, 2019 at 15:09
Yes this is what json_agg does (see linked demo and documentation) — S-Man
– S-Man, Commented Jan 16, 2019 at 15:15

S-Man · Accepted Answer · 2019-01-16 15:02:49Z

1

Do you simply search for json_agg?

SELECT json_agg(speciality) 
FROM talent_speciality

If you are not needing a JSON array but a simple array you could use array_agg of course

SELECT array_agg(speciality) 
FROM talent_speciality

answered Jan 16, 2019 at 14:57

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

1 Answer 1