An array is an ordered linear data structure consisting of a collection of elements (values, variables, or references), each identified by one or more indexes. When asking about specific variants of arrays, use related tags as well. When using this tag, in a question that is specific to a database, tag the question with the database being used.
Questions tagged [array]
324 questions
146
votes
8 answers
How to turn JSON array into Postgres array?
I have a column data of type json that holds JSON documents like this:
{
"name": "foo",
"tags": ["foo", "bar"]
}
I would like to turn the nested tags array into a concatenated string ('foo, bar'). That would be easily possible with the…
Christoph
- 1,653
- 2
- 12
- 8
54
votes
2 answers
Convert right side of join of many to many into array
When using join on many to many relationship the result is split on multiple rows. What I'd like to do is convert the right side of a join into an array so the result is one row.
Example with 3 tables:
CREATE TABLE items (
id serial primary…
Ced
- 754
- 2
- 7
- 10
35
votes
1 answer
Query JSON array of obejcts against multiple values
I want to write a query against a jsonb type table-column in Postgres that, given an array of customers IDs, will find corresponding groups.
Given this example table:
CREATE TABLE grp(d jsonb NOT NULL);
INSERT INTO grp VALUES
…
BartZ
- 453
- 1
- 4
- 4
34
votes
3 answers
Foreign key constraint on array member?
Suppose I have a table containing job roles:
CREATE TABLE roles
(
"role" character varying(80) NOT NULL,
CONSTRAINT "role" PRIMARY KEY (role)
);
Suppose I further have a table, users, and each row (a specific user) can have an arbitrary number…
user2965107
- 441
- 1
- 4
- 3
34
votes
4 answers
How can I get a unique array in PostgreSQL?
The following
SELECT ARRAY[a,b,c,d]
FROM ( VALUES
('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);
Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one of the duplicate foo elements removed? Does…
Evan Carroll
- 65,432
- 50
- 254
- 507
34
votes
1 answer
Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?
I have been playing with arrays in one of my PostgreSQL databases.
I have created a table with a geometry array with at least one element:
CREATE TABLE test_arrays (
polygons geometry(Polygon,4326)[],
CONSTRAINT non_empty_polygons_chk
…
Adam Matan
- 12,079
- 30
- 82
- 96
26
votes
1 answer
How to preserve the original order of elements in an unnested array?
Given the string:
'I think that PostgreSQL is nifty'
I would like to operate on the individual words found within that string. Essentially, I have a separate from which I can get word details and would like to join an unnested array of that string…
swasheck
- 10,755
- 5
- 48
- 89
25
votes
2 answers
How to get particular object from jsonb array in PostgreSQL?
I have a field called 'user' that holds a json array that roughly looks like this:
"user":
[{ "_id" : "1", "count" : "4" }, { "_id" : "3", "count": "4"}]
Now I want a query like:
select count from tablename where id = "1"
I'm not able to get the…
Rabi C Shah
- 251
- 1
- 3
- 3
19
votes
3 answers
When to use JSON or JSONB array vs SQL array in postgres
I am looking for the main points to consider when choosing between a JSON or JSONB array and the regular SQL postgres array. If you know that you will only be inserting one data type into an array, why would you use the SQL array over the JSON…
mmmm
- 293
- 1
- 2
- 6
19
votes
4 answers
Casting an array of texts to an array of UUIDs
How can I cast an array of texts into an array of UUIDs?
I need to do a join between two tables: users and projects.
The users table has an array field named project_ids containing the project IDs as text.
The projects table had a UUID field named…
Sig
- 455
- 1
- 5
- 14
19
votes
2 answers
Why is array_agg() slower than the non-aggregate ARRAY() constructor?
I was just reviewing some old code written for pre-8.4 PostgreSQL, and I saw something really nifty. I remember having a custom function do some of this back in the day, but I forgot what pre-array_agg() looked like. For review, modern aggregation…
Evan Carroll
- 65,432
- 50
- 254
- 507
19
votes
1 answer
Postgres query to return JSON object keys as array
Is it possible to return a JSON object keys as an array of values in PostgreSQL?
In JavaScript, this would simply be Object.keys(obj), which returns an array of strings.
For example, if I have a table like this:
tbl_items
---------
id bigserial NOT…
Yanick Rochon
- 1,651
- 4
- 20
- 28
18
votes
1 answer
Pass an array or record to a function in PostgreSQL?
I have a task to pass arrays, records, and in some cases array of records as a parameter to functions in PostgreSQL.
Worker
- 519
- 2
- 7
- 13
18
votes
1 answer
What is the fastest way to verify that a json[] array is not empty?
I want to verify that an json[] column the_array is not empty. For a normal array, I could check on:
the_array != '{}'
However, that does not work for a json[], as demonstrated below:
select '{}'::json[] != '{}'
ERROR: could not identify an…
Jarius Hebzo
- 455
- 2
- 4
- 10
17
votes
2 answers
How to cast to int array in Postgresql?
I would like to cast from ARGV[] which is text to int array in PostgreSQL where I marked the pseudocode by TODO in the code.
Code in PostgreSQL 9.4.3 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit:
CREATE TABLE…
Léo Léopold Hertz 준영
- 765
- 3
- 9
- 22