Questions tagged [array]
The array tag has no usage guidance.
316
questions
0
votes
1
answer
5k
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 ...
- 227
0
votes
1
answer
921
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",&...
- 103
1
vote
1
answer
3k
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 ...
- 285
1
vote
3
answers
5k
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 ...
- 113
0
votes
1
answer
974
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 ...
- 125
1
vote
2
answers
591
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? {&...
- 225
2
votes
2
answers
4k
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 ...
- 361
-2
votes
1
answer
147
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,
&...
2
votes
1
answer
5k
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 ...
4
votes
2
answers
763
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] |
...
- 219
1
vote
1
answer
560
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 ...
- 219
1
vote
1
answer
253
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 ...
- 115
0
votes
1
answer
363
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&...
- 101
1
vote
1
answer
312
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 ...
1
vote
1
answer
3k
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 ...
- 15
6
votes
2
answers
2k
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 |
----------...
- 219
0
votes
1
answer
2k
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 ...
- 15
2
votes
1
answer
3k
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 ...
0
votes
1
answer
33
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 ...
- 109
1
vote
1
answer
4k
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(`...
- 113
18
votes
3
answers
16k
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, ...
- 283
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 (
...
- 155
0
votes
1
answer
2k
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
1
answer
2k
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 ...
1
vote
1
answer
2k
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',...
- 77
0
votes
2
answers
89
views
How do I speed up a string manipulation query where I want to replace characters, extract certain values and update a table with the results?
I am trying to extract information from strings that are presented in a key-value format, with the keys and values being separated by commas. I want to extract the values associated with certain keys ...
- 25
0
votes
0
answers
16
views
Array of chars? [duplicate]
Can we create an array of chars in postgresql i.e (Column_name char[5]) and does it take only 5 bytes in disk ? since type char takes only one byte
- 35
1
vote
1
answer
694
views
Function that loops through array parameter values to build multiple WHERE clauses (postgres 11.4)
I have been iterating on an idea presented by Rob Conery in his excellent post to generate monthly reports using PostgreSQL Views.
My version required the View to be refactored into a Function as I ...
0
votes
1
answer
5k
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 ...
- 539
0
votes
1
answer
171
views
How do I send an array of strings to PG from PHP without the former becoming confused?
I want to do this, but for more than one string:
SELECT * FROM table WHERE label <> $1
That selects all records from table where the label column is different from a single string. It works.
...
user212833
0
votes
1
answer
1k
views
Postgres query to select all records that have at least one matching array value from another array
Rough table structure:
id uuid
fields jsonb
created_at timestamptz
related_content_ids _uuid
updated_at timestamptz
I'm trying to ...
- 103
1
vote
1
answer
579
views
Update dynamic jsonb array of objects
I am trying to update a jsonb key with a new value, but I cannot update all the keys at once.
My json structure is something like this:
[{
"type": "button",
"content": {
"align": "...
- 83
0
votes
0
answers
2k
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 ...
- 1
2
votes
1
answer
479
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 ...
- 21
2
votes
3
answers
4k
views
Postgres- update a value in a array on particular index
I have a column named ids that is of type bigint[]. ids can 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.
For ...
- 123
1
vote
0
answers
591
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": [\"[\\\"[\\\\\\\"[\\\\\\\\\\\\\\\"[\...
- 111
5
votes
0
answers
11k
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,425
1
vote
0
answers
58
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 ...
- 539
1
vote
1
answer
1k
views
MongoDB: Is it possible to search for two fields whose array sizes don't match?
I have a collection that looks something like the following (I have simplified it a lot). I need to query any record that doesn't have the same number of customersAccepted entries as customersInvited. ...
- 13
1
vote
0
answers
566
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 ...
- 3,425
1
vote
1
answer
961
views
Assign a value to a field of a composite type (a row in an array of rows)?
I am currently writing a function that works with an array of rows. I need to write a value to a particular column of a particular row in the array. The following code should encapsulate the problem I'...
- 63
0
votes
1
answer
2k
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 ...
- 13
1
vote
0
answers
680
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,...
- 121
0
votes
1
answer
645
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 ...
- 195
1
vote
1
answer
761
views
Two array_agg at once
I'm trying to get all likes/dislikes of multiple post.
The problem can be summarized as:
posts table: id, content, user_id (fk)
users table: id, name
likes table: post_id (fk), user_id (fk), is_liked,...
- 113
2
votes
2
answers
1k
views
Build array from boolean columns
I have columns like role1, role2, role3, etc. They are all booleans.
I would like to create a view on this table, that has a roles column of type text[]. If the columns were TRUE, FALSE, TRUE, the ...
- 310
2
votes
1
answer
2k
views
Postgres: Filtering large jsonb arrays coming from a sub-query
I have a table called external_data in Postgres designed with two columns :
+----+---------------------------------------------+
| id | data |
+----+------------...
- 21
0
votes
0
answers
300
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-...
- 135
6
votes
3
answers
661
views
Allow for dynamic result sets from plpgsql function by filtering based on an array passed in?
I think I've avoided the XY problem here, as I'm laying out my solutions to the real underlying problem (summarizing multiple tables in a dynamic fashion) and I'm only asking about the one final part ...
- 588
1
vote
2
answers
8k
views
Mongodb Sort Array Objects within Array using expression
Please help to make query in mongodb.
I want mongo query in which sort array objects using some expression.
Example.
I have documents like
{
"_id:"...
- 135