Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
2 votes
1 answer
48 views

Select rows where array of letters is contained in given array

I want to search for the letters in the "spelling" (text[]) column: a n n o y t I need it to find the words: any, annoy, no, an, toy But not find: derivatives of annoy (annoying, annoyance), ...
user avatar
1 vote
1 answer
68 views

Any performance reasons to prefer idx() (intarray) over array_position()?

Are there performance reasons (or other reasons) to prefer idx (from intarray) over array_position to find the position of an integer in a list of integers? I ask because we've used idx since before ...
user avatar
  • 187
2 votes
2 answers
46 views

does a btree index on an array field actually do anything?

In PG you can create a table like CREATE TABLE foo ( id uuid PRIMARY KEY DEFAULT gen_random_uuid (), name text NOT NULL, things text[] NOT NULL DEFAULT ARRAY[] ::text[] ) and a related index ...
user avatar
  • 131
-1 votes
2 answers
112 views

SQL (Postgres): query array Column which must be a subset of an array

I have a table "article" and for each entry there are some "tags" stored (array column) name | tags test1 | {t1,t3} test2 | {t2,t3} test3 | {t3} test4 | {t1,t5} test5 | {...
user avatar
1 vote
0 answers
18 views

Storing Image Filenames As An Array So I Can Add New Values Without Having To Re-Write The Entire Array - MySQL

I have an image library where I would like to create the functionality where users can create boards of selected images. The boards will essentially be collections of images on the site along a ...
user avatar
  • 111
1 vote
1 answer
59 views

Aggregate multiple columns and rows into a json array

I try to query multiple columns and rows from a table to create the xy-pairs for a step-linechart in json format. I need the json like this: [[63115200,213.4], [139769280,213.4], [139769280,213.39], [...
user avatar
0 votes
1 answer
44 views

Convert right side of join of many to many into array of objects

I have 2 tables: Table subjects: id, class_id, title, created_at, updated_at Table marks: id, student_id, class_id, mark, created_at, updated_at, subject_id I want to query subjects table and have it ...
user avatar
  • 3
1 vote
1 answer
114 views

Unnest array of arrays

In my parameterized query I have to unnest arrays with 1 dimension and 2 dimensions: $1: ARRAY['id1', 'id2'] $2: ARRAY[ARRAY['tag1'], ARRAY['tag2']] I tried this query: INSERT INTO table (id, tags) ...
user avatar
0 votes
1 answer
96 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 ...
user avatar
  • 5
0 votes
1 answer
218 views

psql function: append one dimensional array to a two dimensional array using array_append

I tried to append an one dimensional array to a two dimensional array using array_append in psql function. I defined count_values as output by out count_values integer[][], cout_value as variable in ...
user avatar
1 vote
1 answer
73 views

How do I split an array between GROUP BY groups?

I'm trying to create a query which will put some rows in multiple groups. I'm doing this by generating an array that contains both categories I want to group by. Here's my query so far (slightly ...
user avatar
1 vote
2 answers
59 views

Converting results to array

I have many entities like: user id | name ----------- 1 | Joe 2 | David 3 | Jane cars id| name ------------ 1 | cars1 2 | cars2 3 | cars3 4 | cars4 5 | cars5 6 | cars6 7 | cars7 8 | cars8 ...
user avatar
  • 15
1 vote
2 answers
86 views

Does the PostgreSQL array inTOAST storage have random access times?

This question is related to PostgreSQL TOAST storage and the GIS.SE question: Should TOAST compression be disabled for PostGIS? Basically, I was wondering if there is any guarantee of constant time ...
user avatar
  • 2,797
0 votes
1 answer
38 views

Postgres outputting an array in PSQL

I have the following function. It is something I am building and want to show the contents of stuff_list so I can prove it is working correctly. I can't however work out what output type I should be ...
user avatar
0 votes
1 answer
489 views

Unnest array values from multiple columns with dynamic unpivot

I have been given several tables in wide form that I would like to convert to long form. Unfortunately, they have about 60 columns. Example input: CREATE TABLE tbl_wide ( p_key integer PRIMARY KEY,...
user avatar
0 votes
1 answer
88 views

How to loop an array and sum values until condition is met?

I have a relational table where I need to loop through and sum amount values until a specified amount criteria is met. The data looks like this: Company Account Amount Coastal A384 500 Coastal C940 ...
user avatar
1 vote
1 answer
50 views

PLPGSQL store columns of a 2D query output into array variables

In some step of a plpgsql function I need to store a 2D query result into array variables. The following code does the job for scalars (but fails with arrays): SELECT col_a, col_b FROM my_table WHERE ...
user avatar
  • 181
1 vote
1 answer
88 views

Using the array result of a select statement returns ERROR: pq: operator does not exist: integer = integer[]

The query in question is the following: SELECT * FROM options WHERE id = any(SELECT option_ids FROM workshops WHERE id=3) ERROR: pq: operator does not exist: integer = integer[] Basically the ...
user avatar
0 votes
3 answers
499 views

How can I return multiple columns from an array of ids?

I have a two tables like the following: workshops table +----+--------------+------------+ | id | name | option_ids | +----+--------------+------------+ | 1 | Conversation | {2,3} | +----...
user avatar
4 votes
1 answer
89 views

Optimize query matching first N items of an array

I'm working with a Postgres DB full of chess game data right now, where each game is a row in a 'records' table. The players' moves and the (optional) computer evaluations of those moves each have ...
user avatar
0 votes
0 answers
21 views

Multiple tokens per account

In the context of user account management, I'm using Firebase. So for each login, the front-end sends an Auth ID, a FCM token (for sending notifications to the user), and the usual user details. Each ...
user avatar
  • 669
5 votes
1 answer
102 views

Match .csv values as INTs. If one value from a group in the .csv matches one in another group, then merge strings

Here we have two sets of numbers. The problem is that I can't figure out how to get from the input to the output of numbers (DDL and DML below and also in the fiddle here). current_data 1,2,3 1,4 1,5,...
user avatar
0 votes
1 answer
272 views

Calculating Average Value of JSONB array in Postgres

I have a column called "value" in my "answers" table. | value | |---------| | [1,2] | | [1] | | [1,2,3] | The type of "value" is "jsonb". I want to get ...
user avatar
0 votes
0 answers
197 views

CROSSTAB / cannot extract elements from a scalar HELP PLEASE!

I'm kind of lost here and I really need some wisdom from a Data Expert: cf.item ->> 'name' AS "custom_field" they want everything as a column and not create them as a row and cf.item -...
user avatar
3 votes
1 answer
656 views

How can I match the items in an array column, with a different table in Postgres?

I have a table in a Postgres 12 database with that has an array column with a bunch of dog breed guids. I want to lookup these values and basically return the lookup values instead of the guids. ...
user avatar
0 votes
0 answers
129 views

Postgres jsonb field: remove an element from an array by the element id

Figure a Postgres 12.5 jsonb field containing the following: [ {id:1, name: 'one'}, {id:2, name: 'two'}, {id:3, name: 'three'} ] How to write the request that will match: "drop the object ...
user avatar
  • 197
2 votes
1 answer
2k 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 | +------------+ | ["...
user avatar
  • 327
0 votes
1 answer
33 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 ...
user avatar
  • 2,797
1 vote
1 answer
384 views

How to keep array fields in PostgreSQL jsonb

It seems that PostgreSQL (12+) cannot keep the PostgreSQL array type in jsonb objects. If a PostgreSQL array is stored in a jsonb, it seems that it's converted to a jsonb array. For example, given the ...
user avatar
  • 2,797
0 votes
1 answer
988 views

Postgres Query Array Column with Array

I have a postgres view "people_codes" that has a column that is an ARRAY_AGG of intergers. I am trying to query on the view with an array of integers for all rows that may have one of these ...
user avatar
0 votes
1 answer
105 views

Join two Json arrays to one with key and value

I have two jsonb columns(keys,values). Eg: keys colum value = ["key1","key2","key3","key4"] values column = ["val1","val2","val3",&...
user avatar
  • 103
0 votes
1 answer
130 views

Postgresql: sort by value position in array column, then by secondary order

I'm not quite sure what the best way to phrase this is... So in my DB there is pillars text array which is basically an enum where providers ordered what values meant the most to their business, from ...
user avatar
1 vote
3 answers
1k views

Postgresql: 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 ...
user avatar
  • 113
0 votes
1 answer
190 views

Uniqueness constraint for elements of arrays in the entire table

In the following table: CREATE TABLE user ( email varchar[], ... ); I'd like to ensure that no two users have the same e-mail. I'm not sure how the UNIQUE constraint interacts with ...
user avatar
1 vote
2 answers
161 views

Zip array with values from JSON

Say I have '{1, 2, 3, 4, 5}' (just an array with the numbers 1-5), and {"1": 0, "2": 3, "5": 0} (comes from a column). With only SQL, is it possible to output this? {&...
user avatar
  • 205
1 vote
2 answers
743 views

Postgres 11 JSONB - How to get all distinct values of a field across all elements

I have a table called Shipments: CREATE TABLE shipments ( shipmentId numeric, metadata jsonb ); Inside the metaData column there can exist an array of JSON objects named stops which ...
user avatar
  • 321
-2 votes
1 answer
46 views

Are unstructured databases faster using arrays or many binary variables? [closed]

Is it faster (or otherwise better) to structure unstructured data like this: { label:"many_variables_example", "a":0, "b":1, "c":1, "d":1, &...
user avatar
1 vote
1 answer
2k views

How to get a JSON array without JSON_ARRAYAGG in MySQL

I can't use a JSON_ARRAYAGG function introduced in MySQL 5.7.22 because I'm using MySQL 5.7.20. I would like to get a JSON array of a column values like I would get if I could use the function: SELECT ...
user avatar
4 votes
2 answers
67 views

Unnest ids from arrays based on priority with no duplication

Here is the sample schema for my database: Priority | Productive | UnProductive | Neutral | ------------------------------------------------- High | [1, 2] | [] | [4, 5] | ...
user avatar
  • 209
1 vote
1 answer
55 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 ...
user avatar
  • 209
1 vote
1 answer
31 views

Having regex_matches exclude cases where both capturing groups match a 0-length string

I'll trying to write a regex pattern that will help me create an array of length 2 arrays such that each inner array is matched like so: I'm looking to split sequences that begin with a positive or ...
user avatar
  • 115
0 votes
1 answer
36 views

Conditionally insert a value in an array when creating a new column

I have some data in a jsonb col that I need to clean and insert into another text array column. What I have: [ { "v": "birthday" }, { "v": "reference&...
user avatar
  • 101
0 votes
1 answer
70 views

Concurrent Update of Array (Postgres)

I have an array that I can update from multiple workers as follows: UPDATE my_table SET arr = array_append(my_table.arr, element). The problem pops up when two workers try to update the array at the ...
user avatar
0 votes
1 answer
831 views

Postgres 12: Inserting array of composite types, composite type has an array field. Malformed array literal, unexpected end of input

I'm having an issue with inserting a simple array of integers into my table in Postgres via a procedure. The array of integers is declared within a composite type and I'm trying to insert an array of ...
user avatar
  • 5
5 votes
2 answers
589 views

Postgres not returning data on array_agg query as below

The problem arise when there are no data for books in specific library. Consider a following working scenario. Table library -------------------------------- | id | name | owner | ----------...
user avatar
  • 209
0 votes
1 answer
646 views

(PostgreSQL 11) Reading in an array of JSON objects (or composite types) from a CSV file into Postgres

So I am trying to import rows into a table using a CSV file, but have been running into problems I haven't been able to solve. I would like one column of a row to be an array of JSON objects. How I ...
user avatar
  • 5
2 votes
1 answer
787 views

Dynamically update JSONB rows - POSTGRESQL

I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field. The node we are trying to move, however, is not ...
user avatar
0 votes
1 answer
19 views

Join static file xml with ID field with Database table ID field [closed]

I am using php to display a table with different columns. The table is build from a static xml file with several hundreds of entries. Each entry has also an ID field. I have to look up that ID field ...
user avatar
  • 109
1 vote
1 answer
767 views

Mysql JSON update by value of a key

I have a table like: CREATE TABLE `campus_tb` ( `campus_id` int(11) NOT NULL AUTO_INCREMENT, `campus_dataJSON` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`...
user avatar
  • 113
9 votes
3 answers
6k views

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, ...
user avatar
  • 193

1
2 3 4 5 6