Questions tagged [array]

The tag has no usage guidance.

41 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
4
votes
0answers
1k views

Does PostgreSQL support `unnest` as a column?

Does PostgreSQL document and support this behavior of the SRF unnest() as a computer/virtual column, SELECT unnest(array_agg(id)) AS id FROM foo GROUP BY CUBE (a,b,c,d) HAVING count(*) > 1 If ...
3
votes
0answers
2k 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 ...
3
votes
0answers
542 views

Best way to cast array (text[]) to record-type?

PostgreSQL has the ability to create record types. A lot of operations like regexp_match return text[]. What is the most efficent way to go from text[] to a custom record type? Currently, I'm doing ...
2
votes
0answers
132 views

Copy to file and transform arrays

I'm copying data from a Postgres DB on AWS RDS to a Postgres-like DB on AWS Redshift. Varchar arrays are being exported as {foo,bar} but Redshift wants them as ["foo","bar"]. Redshift doesn't ...
2
votes
0answers
762 views

PostgreSQL: Storing large volume of photos and attached tags: many-to-many relationship or array of text?

This is my first post here and I am quite new to PostgreSQL - many apologies if my question may appear a bit unclear. We set up a database for storing a large volume of photo-metadata with attached ...
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 ...
2
votes
0answers
509 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 ...
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 ...
1
vote
1answer
21 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 ...
1
vote
0answers
244 views

Postgres Unnest or flatten a nested array inside of JSONB

I've got a table MY_TABLE that has the following columns: User Name Data Purchased Data is of the form : [{ "Id": 1, "Attributes": [\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\...
1
vote
0answers
18 views

Can I assign an index to an exploded array?

I'm doing a LATERAL EXPLODE on an array in Hive, is there a way of reliably assigning a row number based on the array element? It looks like calling row_number() on the results of the LATERAL EXPLODE ...
1
vote
0answers
34 views

automatic cast of anonymous record array type to the array type of a named composite type in PostgreSQL?

From PostgreSQL documentation, it is possible to cast an anonymous record type to a named composite type at a function call site. Quoting the example there: CREATE TABLE mytable(f1 int, f2 float, f3 ...
1
vote
0answers
409 views

Nested data in postgres

I have a CSV with columns of various data types, including what i would interpret as nested data. The nested data comes in many forms. For example: [13,11,23,18,18,21,23,16,8,20,22,18,20,17,13,10,8,...
1
vote
0answers
64 views

Postgresql 10 arrays equality failure

I am using AWS RDS postgres instance version 10.3. Completly stuck with the problem. I have a table like: create table social_message( social_profile_id int8 not null, body text, subject ...
1
vote
0answers
959 views

Table structure to store a an array of tags per entry

Lets say one wants to create a DB to store items, each having a Set<String> tags, associated with it. A straightforward way would be a flat table with ItemID, ItemName, DateCreated,... and a Set&...
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 ...
1
vote
0answers
539 views

Inner join on array columns from 3 tables

I have 2 tables that are related between them using integer data which is stored in arrays. Data table telemetria_data.historico_354677050164513 CREATE TABLE historico_354677050164513 ( imei ...
1
vote
0answers
252 views

Schema for ACL/User/Group/Role management

I'm building an app with some fairly complex permission and ACL rules and want to get my schema right. These are my goals: A user signs up on behalf on an organization. Organization has groups. ...
1
vote
0answers
49 views

ANY in postgresql gives weird error

when I do: SELECT ARRAY[4] I get array integer[] ------ {4} and if I do: select * from b where b.ID = ANY (array [4]) That works. Now, something else: select array_agg(4) I get: ...
1
vote
0answers
246 views

Is there are way to use Fine Grain Auditing to record Varray bind values?

I have an application, and would like to use Oracle's FGA (Fine Grained-Auditing) to record varray bind information. Specifically, I am using Oracle Spatial and need to know at what extent users are ...
1
vote
0answers
2k views

storing arrays or images in a database table

I'm a beginner with both database design and use of forums such as this one so forgive me if I forget to provide some relevant information below. Basically I'm trying to store a set of arrays, in ...
0
votes
0answers
41 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 -...
0
votes
0answers
33 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 ...
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 ...
0
votes
0answers
30 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
67 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 ...
0
votes
0answers
19 views

Why are arrays of rows of mismatched types allowed in PostgreSQL?

I'm suprised that I can do the following. Why is that allowed? Doesn't it violate some sort of type check? Or am I misunderstanding what it is actually doing? postgres=# SELECT ARRAY[ROW(1), ROW(...
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
0answers
848 views

MariaDB: SQL query to return array of json objects and remove `\` from response

I am creating a sql query for api response in javascript. But i stuck on one part that is creating a array of json objects of same group. I have checked with the json_arrayagg and also ...
0
votes
1answer
860 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
0answers
60 views

Extract Image URLs from html text using Postgres sql Query

I have a column in postgres db in which html contain stored. Like this <p style="text-align: justify;"><strong><img alt="" src="https://images.unsplash.com/photo-1579121477063-...
0
votes
1answer
543 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
39 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 ...
0
votes
0answers
1k views

How to pass array of UUIDs *OR* empty array and return implicit (inner join)?

How would I pass a list that is either full of UUIDs or empty, then return only joined records with those id values or all records if empty? This question (Casting an array of texts to an array of ...
0
votes
1answer
859 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
1answer
203 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 ...
0
votes
0answers
1k views

Convert JSON array to native integer array

My function is returning json data which is containing two integer array. Now I need to get this two integer array list and passed it to another function. CREATE OR REPLACE FUNCTION method1(id ...
0
votes
0answers
570 views

MySQL version of split_to_array or split_to_table?

PostgreSQL supports two String Functions, that are incredibly useful regexp_split_to_array regexp_split_to_table Both of these functions essentially take two strings, (a) a pattern and (b) an input ...
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
406 views

Checking of one specific element of one array is in another array?

Suppose I have two arrays. One looking like (3, 9, 10, 39), and one looking like (4, 10, 9, 48, 391, 2). How would I (through pure MySQL) check if one of the elements in the first array is inside the ...
-1
votes
1answer
1k views

Postgresql JSONB Column, how to query for multiple specific elements in a JSON array

Giving the following JSONB data structure of a stopInfo column in a myTable table: { "stopInfo": [ { "stopType": "Origin", "partnerId": "KR01", "locationId"...