Questions tagged [array]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0
votes
0answers
40 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 -...
3
votes
1answer
167 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. ...
0
votes
1answer
841 views

How to remove NULL values in multidimensional array?

SELECT dt.name,dt.description,dt.odobj,dt.defval,dt.datatypeid ,array_agg(array[dtv.description::text,dtv.odobj::text,dtv.name::text,dtv.datatypevalueid::text,dtv.datatypeid::text]) as ...
0
votes
2answers
1k views

PostgreSQL: Find row with JSON object attribute in array

I have a postgres database which is set up like this: CREATE TABLE arr_test ( id serial primary key, data jsonb ); INSERT INTO arr_test (data) VALUES ('{"test_row": 1, "my_arr": [{"serial":"...
0
votes
0answers
28 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 ...
1
vote
1answer
431 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 ...
1
vote
1answer
197 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 | +------------+ | ["...
5
votes
1answer
12k views

How does unnest() treat NULL values and empty arrays?

I am trying to achieve something like LEFT JOIN with array unnest() function. I want the query to return a row with a null value if the array is empty. Therefore, by using a CASE construct, I wanted ...
0
votes
1answer
588 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
0answers
16 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 ...
1
vote
1answer
22 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 ...
0
votes
1answer
2k views

Testing a Hive array for IS NULL says not null

I have a table containing an array, and I want to check if it is empty or NULL. It appears that I cannot check the NULL directly! Can anyone shed light on why the NULL check isn't working? create ...
0
votes
1answer
35 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 ...
0
votes
1answer
31 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",&...
0
votes
1answer
202 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 ...
2
votes
1answer
1k views

How to get lexemes array from Postgresql ts_vector, sorted by lexems positional information?

Postgresql full text search to_tsvector() function returns a sorted array of lexemes. I need exactly the same function, but returning unsorted array of lexemes. Is there any such postgresql function ...
0
votes
1answer
534 views

Is possible to insert an array inside another array in postgresql?

Let's say I have a table in postgresql called 'Test' which contains a single column called 'Column1' of the type int[]. So, it can contain an array of integers for each row, for example, {1,2,3}. I ...
0
votes
1answer
857 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....
1
vote
1answer
162 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 ...
25
votes
1answer
45k views

PostgreSQL JSON query array against multiple values

I want to write a query against jsonb type 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 ...
0
votes
1answer
37 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 ...
1
vote
3answers
135 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 ...
0
votes
1answer
41 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 ...
1
vote
2answers
42 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? {&...
5
votes
2answers
15k 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&...
-2
votes
1answer
29 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, &...
1
vote
2answers
59 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 ...
4
votes
2answers
54 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] | ...
1
vote
1answer
28 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 ...
3
votes
3answers
2k 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, ...
1
vote
1answer
25 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 ...
0
votes
1answer
27 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&...
11
votes
2answers
18k views

Efficient merging (removing duplicates) of arrays

I have two tables, left2 and right2. Both tables will be large (1-10M rows). CREATE TABLE left2(id INTEGER, t1 INTEGER, d INTEGER); ALTER TABLE left2 ADD PRIMARY KEY (id,t1); CREATE TABLE right2( t1 ...
0
votes
1answer
27 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 ...
0
votes
1answer
205 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 ...
1
vote
1answer
204 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 ...
5
votes
2answers
256 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 | ----------...
0
votes
1answer
208 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 ...
2
votes
1answer
129 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
1answer
16 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 ...
1
vote
1answer
75 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(`...
26
votes
2answers
31k 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:...
3
votes
1answer
426 views

Have several 128 elements lists, need to obtain distance between them

Is storing them in a CUBE type field and then getting euclidean distance, (using <->) the only way? I can not save them as a list of floats, since i get: "can't adapt type numpyndarray" Can ...
1
vote
3answers
903 views

Postgres- update a value in a array on particular index

I have a column named ids that is of type bigint[]. ids cane be null or of any length It has values based on certain hierarchy. My use case is I want to update value that is at a particular index. ...
16
votes
1answer
38k views

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

check if each value of array are equal

I try to get d.id only if all rows with same id have same pday: with dif_zero as (d.id, date_part('day', p.docdate)::text pday from sh_billing.dogovor d join sh_billing.pays p on p.dogovor_id = d.id ...
0
votes
0answers
29 views

How to store very very big Array in MySQL (separator / json_encode / serialize)?

I have project with saving timestamps from SCADA system to localhost MySQL-DB. All works fine but thinking about storing datas (read many threads and array storing isnt most efficiency method). Every ...
0
votes
1answer
66 views

Postgresql - Distinct array aggregation

This is my current query SELECT project_id, release_id, array_agg(DISTINCT version_bom_id) AS version_bom_ids, sum(case when review_status='REVIEWED' then 1 else 0 end) AS reviewed_count, sum(case ...
5
votes
2answers
17k views

Use array of composite type as function parameter and access it

I have created a type Books in Postgres which has 2 numeric fields and 2 varchar fields. I want to send an array of Books to a function to INSERT those values in a matching table. This is my type: ...
1
vote
1answer
44 views

How to write correct array string parameter for mysql query?

I'm trying to put up a correct string to pass as a parameter to a simple query. Correct query that returns records: SELECT id_s FROM group_of_id_s WHERE date IN ('2020-08-01','2020-08-01','2020-08-02',...

1
2 3 4 5 6