Skip to main content

Questions tagged [array]

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.

Filter by
Sorted by
Tagged with
0 votes
1 answer
1k views

Use ENUM in array of objects

Is it possible to setup an ARRAY column with objects/JSON on a TABLE, forcing a parameter of the objects in the ARRAY to adhere to an ENUM, while keeping that object parameter unique? Data examples: ...
0 votes
0 answers
20 views

What are downsides of using composite types and array type columns in TimescaleDB hypertable?

I'm considering to use a composite type, array of standard type and array of composite type columns in TimescaleDB hypertable. I'm curious whether using such columns has any downsides like less ...
12 votes
2 answers
42k views

Find rows containing a key in a JSONB array of records

I'm trying to query for a key present in an array of objects. This structure: column jdata {"name": "Somedata", "array": [ {"name":"bla1", "attr&...
0 votes
1 answer
17 views

MongoDB createIndex compound unique index on array field

I'm trying to create a unique index in MongoDB that enforces uniqueness based on the existence and non-undefined nature of a field (barcodes) in my skus collection. Here's the index creation query I'm ...
0 votes
2 answers
514 views

Storing arrays of data in a time-series database

I'm building an low utilization time-series database to capture yearly data points for a set of items fewer than 100,000. My question has to do with storing arrays of data in a way that is easily ...
1 vote
1 answer
956 views

In Postgresql, can I create a column of a one-dimension array type with foreign key constraint? If not, how to simulate it?

In essence I have to design todo table, and an item table. I need to preserve item order, as well as allowing client to rearrange the order. If postgres allow an array of foreign key that would be ...
5 votes
2 answers
26k views

How do I select arrays that are not empty?

Why is this so tricky, what is token set to that it isn't equal to null nor an empty string? SELECT lexemes FROM ts_debug('This is a title') WHERE alias = 'asciiword'; lexemes --------- {} {} {}...
10 votes
1 answer
12k views

How to get 0 as array_length() result when there are no elements

I have a query like this: select id, array_length(users_who_like_ids,1) as ct from queryables order by 2 desc; But empty arrays (with no elements) sort on top. I'd rather have array_length() return 0 ...
2 votes
1 answer
662 views

Creating a TYPE that is an Array within a Function (or a predefined Array TYPE) in PostgreSQL

I am researching migration of a major system from Oracle to PostgreSQL. Getting into coding functions now, and am looking to see if there is a compatible object in PostgreSQL where, dynamically ...
25 votes
1 answer
26k views

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 ...
1 vote
1 answer
49 views

MySQL Trigger updating second table based on History and computed values

The Goal: Update an secondary table called Lottery_Cycle when an INSERT occours on table Lottery_History with some computed values and checking previous data. Let's say a lottery with 25 numbers of ...
1 vote
1 answer
472 views

Find integer in array is very slow

PostgreSQL Version PostgreSQL 15.0, compiled by Visual C++ build 1914, 64-bit This is my "victim" table. create table if not exists "KeywordsStats" ( id bigserial primary key, &...
6 votes
1 answer
13k views

How to change array_agg() to return empty array on empty input in PostgreSQL?

I am trying to write a PostgreSQL array_agg() variant that returns the empty array '{}' instead of NULL. Trying to avoid the performance hit of custom aggregate functions with CREATE AGGRGATE, I tried ...
0 votes
1 answer
8k views

Array of strings when updating a field

Unintentionally discovered that following query works in PostgreSQL: UPDATE "sometable" SET "somefield" = ('string1', 'string2') WHERE "id" = 1; I'm passing an array of strings to field and the ...
1 vote
1 answer
668 views

Multi dimensional JSON Array SQL Query

I'm struggling to write an appropriate query for my data -> "schools":[ { "org_symbol":"School 1", "criteria":[ [ ...
1 vote
1 answer
2k views

Mongodb what is the cost of array queries?

As far as I can tell, find queries in mongodb involving an array appear very expensive. Take as example the queries given in: https://docs.mongodb.com/manual/tutorial/query-array-of-documents/ db....
0 votes
1 answer
308 views

Unstructed data field: Query all values from array of objects by key

I have a table that has a unique ID, and a second 'collumn' named 'data' that contains simple key/value items like: "nickname": "value" "fullName": "value" "office": "value" "unity": "value" and a ...
2 votes
1 answer
742 views

Filter by list of values pairs

I have Postgresql 9.4 database and products table. For each product there must be list of pairs: price; quantity for which this price is acutal Products table may contain millions and billions of ...
2 votes
4 answers
6k views

How to pass an array to trigger function in TG_ARGV?

I've got some fairly generic Postgres trigger functions that I'd like to re-use across a few different tables. To that end, I'm passing arguments as part of the CREATE TRIGGER statement so they'll be ...
1 vote
1 answer
650 views

Remap array columns to single column with value

Here is the sample database schema. | id | Critical | High | Low | -------------------------------------------- | 1 | {apple, ball} | {cat} | {dog, egg} | Now I want to remap these to ...
0 votes
1 answer
1k views

postgres reverse jsonb array

Postgres 13 I have a table with jsonb array and I need to revert the array. Any idea on how I can do that? CREATE OR REPLACE FUNCTION array_reverse(anyarray) RETURNS anyarray AS $$ SELECT ARRAY( ...
0 votes
2 answers
822 views

Powershell dbatools combining result of multiple Get-dbaxxx commands to generate a report

I'm new to powershell and dbatools. It is a great tool but when I try to combine the result of few different commands into an array to be exported to csv, it is not providing the intended result. It ...
3 votes
3 answers
5k views

PostgreSQL - summing arrays by index

I have an array of doubles column (double precision[]) in PostgreSQL that keeps half hour values for a day. So each array holds 48 values. I need an efficient query that is summing all this array ...
0 votes
0 answers
132 views

An alternative to PostgreSQL union?

In the following example variables in single quotes '' are arguments passed to the PostgreSQL query. If 'tag_args' is an empty array, the first row of result table is a row of three nulls and I use ...
2 votes
2 answers
16k views

How can you insert data into a ARRAY column on a Snowflake table using SQL?

I am having difficultly finding documentation on how to insert data into an ARRAY column type using SQL on a Snowflake table. Snowflake Documentation: https://docs.snowflake.net/manuals/sql-reference/...
16 votes
4 answers
56k views

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 ...
-1 votes
1 answer
188 views

Replacing substrings in array elements

I have a table with an array of URLs in column urls. Now there's a need to update some URLs changing the hostname. E.g.: {https://storage.host1.com/file.jpg, https://storage.host1.com/file2.jpg} ...
0 votes
1 answer
114 views

Performance issues with @> operator in postgres 14

CREATE TABLE public.article ( id uuid NOT NULL, tags _text NULL, CONSTRAINT article_pkey PRIMARY KEY (id) ); I have the requirement to load articles that have a tag which is in a large ...
25 votes
2 answers
83k views

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 ...
54 votes
2 answers
72k views

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:...
0 votes
1 answer
12 views

How can I get a linear forecast in BigQuery?

BigQuery has no native support for linear forecasting outside of machine learning, but it does support user-defined functions, and these can accept arrays as inputs. However, (as of 2023/10 anyway), ...
0 votes
2 answers
1k views

How to case text array elements automatically?

According to PostgreSQL documentation on the ARRAY constructor: By default, the array element type is the common type of the member expressions, determined using the same rules as for UNION or CASE ...
8 votes
1 answer
24k views

How to remove known elements from a JSON[] array in PostgreSQL?

I'm facing an issue regarding using the JSON data type in PostgreSQL. I try to achieve storing a Java model denormalized in the DB. The model features lists of complex objects. Thus, I decided to ...
0 votes
1 answer
321 views

Convert PostgreSQL array type

Given an enum type (e.g. letter). What is the syntax to convert an existing array to the enum type? The existing values are valid for the enum. For example, how would I convert letter_data in: create ...
0 votes
0 answers
50 views

Create index on array of composite types

I have this example create type price_schedule_day as enum ('monday', 'tuesday', 'wednesday', 'thursday', 'friday', 'saturday', 'sunday'); create type price_element as ( price_start ...
1 vote
1 answer
256 views

PostgreSQL - PostGIS ST_SetValues - int array cast to double and back

I am using ST_SetValues with array defined as ARRAY[[9, 9], [9, 9]]::double precision[][]. If I store 32bit integer value in this array can I retrieve this value exactly after casting from double to ...
7 votes
2 answers
11k views

How does postgres store array values?

Postgres seems to do some sort of compression on array values. I have a table with 2.1 million rows, each has 2 columns of smallint arrays, all populated with 1440 values. most of these values are -...
12 votes
2 answers
14k views

Combining array_agg and unnest

Given a dataset (with a GIN index on values): key | values ------------- 1 | {4,2,1} 1 | {2,5} 2 | {4,1,3} I want to aggregate the arrays: key | values ------------- 1 | {4,2,1,5} 2 | {4,...
34 votes
1 answer
78k views

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)...
12 votes
3 answers
33k views

How to unnest and GROUP BY elements of a JSON array?

Given the band table, with a json column holding an array : id | people ---+------------- 1 | ['John', 'Thomas'] 2 | ['John', 'James'] 3 | ['James', 'George'] How to list the number of bands each ...
2 votes
2 answers
2k views

How to get lexemes in original sort order from ts_vector?

Postgres' full text search to_tsvector() function returns a sorted array of lexemes. I need exactly that, just with lexemes in original sort order. Is there any such Postgres function, or do I need to ...
3 votes
1 answer
1k views

Searching INET[] for CIDR match, efficiently using indexes (without UNNEST)

This question stems from having recently discovered that the following are very, very different in terms of performance DROP TABLE IF EXISTS dns_lookup; CREATE TABLE IF NOT EXISTS dns_lookup ( ...
0 votes
1 answer
55 views

How to calculate quantity based on a boolean field and retrieve values from a JSON field in MySQL

I'm working with a table called price_options that has the following fields: id, slots, list_slots, and quantity. Issue Description My objective is to calculate the quantity for each price option ...
140 votes
8 answers
455k views

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, ...
0 votes
1 answer
190 views

Snowflake - extracting country/ state names - RegEx or Array?

I have a bunch of sloppy Geo text in a field. However the country/ state names or codes are relatively clean. It'll say Poland 23 or Illinois Remote or Frogballs, Germany or TX, AL, AK. I have a ...
3 votes
3 answers
13k views

Is it possible to remove a JSON array element by value in MySQL?

In MySQL, it's possible to remove an array element by index: SELECT JSON_REMOVE('["a", "b", "c"]', '$[1]') `result`; +------------+ | result | +------------+ | ["...
1 vote
2 answers
3k views

Group by unique items in an array column and count records that contain each item

I have a table that looks like this: records ------------------------------------- id items[] ------------------------------------- 1 {toaster,piano} 2 {car} 3 {car, toaster} 4 {tv}...
1 vote
2 answers
760 views

How to update each value in an array with a function?

I want to trim spaces from each value in a text[]. If this was just a text column I could do: update server set tags = rtrim(ltrim(tags)); However how do I do this for an array? As otherwise that ...
3 votes
3 answers
51k views

How to select multiple values into an array and loop through? (postgres 9.3)

I have simple table for the sake of argument. I have a function that selects ids and loops through them called loop_test. I can select an array of ids and loop through them, causing my changes in a ...
7 votes
2 answers
3k views

How to store high-dimensional (N > 100) vectors and index for fast lookup by cosine similarity?

I am trying to store vectors for word/doc embeddings in a PostgreSQL table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm ...

1
2 3 4 5
7