I have an Oracle 19 database with a JSON aggregated array which I need to cut off and repeat after a set number of rows, for example: …etc. Here’s some basic SQL to illustrate how my current dataset is put together: I need to push customer data to an API endpoint. Pushing 1 customer record at a time is not
Tag: arrays
bigquery transpose and concatenate for each record
I want to achieve the following transformation. I have last_name stored in a repeated record as follows. data before transformation I want to achieve the following. data after transformation Example with sample data created. I’m not sure either if I should store it as an array instead of a concatenated field but it would be good to know how to
how to update and append values to array in google spanner
I have a column with data type Array(String) in spanner. How can I append value to an array for update queries I’m able to update the values using this command But the problem with this update is it overrides the previous values. I want to append these values to the ones that are already present. Is there any command that
How to achieve conditional array aggregate?
For simplicity I’ll use the following patient and appointment tables. What I want is one report that contains the following data. So what I need to do is: Get the patient names from patient table aggregate all appointment statuses of the patient aggregate all appointment times of appointments that have status=’active’ So the initial query that I created is: DBFiddle
How to do the equivalent of ‘distinct’ on array field in BQ?
Let’s take the following data: It can also be generated in BQ with the following statement: How would I do the following two ‘distinct’ totals on the right of the following: That is, I want to get a “total” that doesn’t double-count, or rather, only gets the distinct items based on the RowID. Answer If I understand correctly, it is
Remove Null from For loop of ArrayList
I am currently converting an ArrayList to a String to send it to a DB so I can retrieve it on the other end and convert it back to an ArrayList later. My thought process is to convert it to a string …
Compare two arrays in PostgreSQL
I have a table in postgres with a value column that contains string arrays. My objective is to find all arrays that contain any of the following strings: {‘cat’, ‘dog’} The following query uses ANY() to check if ‘dog’ is equal to any of the items in each array and will correctly return rows 1 and 3: I am trying
PostgreSQL array overlap with the ALL construct
I want to achieve the same behavior as in next example: select array[1, 3] && array[1] and array[1, 3] && array[2] using the ALL construct like so: select array[1, 3] && all(…
How can I split a string into character in Snowflake?
I need to split a string like “abc” into individual records, like “a”, “b”, “c”. This should be easy in Snowflake: SPLIT(str, delimiter) But if the delimiter is null, or an empty string I get the full str, and not characters as I expected. Answer Update: SQL UDF I found this problem while working on Advent of Code 2020. Instead
How to query all rows where a given column matches at least all the values in a given array with PostgreSQL?
The request below: returns a list like this: How to get the ids for which id must have at least a and b, and more generally the content of a given array ? From the example above, I would get: Answer For two values, you can use windowing boolean aggregation: A more generic approach uses array aggregation: