So I have 2 Json arrays that need unnesting, and joining based on a key within the json structure. In theory is easy, but without having a ‘left join unnest’ functionality, it all becomes messy. I have achieved what I want, by grouping the results; but I also have concerns that it is doing 2 cross…
Tag: unnest
Create a PostgreSQL view from a unique list of exploded data
When I make this request: I get results like this: Each line has values separated with / (space, slash, space). How to create a view with a list of unique values with count ? Answer You can split the string to rows with regexp_split_to_table() in a lateral join, then aggregate: Demo on DB Fiddle: val | cnt :&…
How to iterate over PostgreSQL jsonb array of objects and modify elements?
Given jsonb array and PostgreSQL 12: Need to convert it to: Is it possible somehow to iterate over jsonb array and downcase only “type” values? I tried: but it separates data and type pairs into separateelements. Answer You need an intermediate level of nesting to rebuild the objects before you ag…
Split value into multiple rows
The following are two tables I have in the database mavenmovies. testLocation (TABLE 1) CREATE TABLE mavenmovies.testLocation ( id INT AUTO_INCREMENT PRIMARY KEY, State varchar(255), name …
How to convert data into this form in SQL :
Input: I have to Convert data from input to output. where we trying if for id freq is n then create n rows of that id. Output: Answer In Presto, one option uses sequence() and a lateral join to generate the rows:
Perform UNNEST, INNER JOIN and then ARRAY_AGG as part of an UPDATE query
I am trying to unnest an array from one table using ORDINALITY to preserve order, then perform an INNER JOIN on another table to find the corresponding value from a specific column and then use ARRAY_AGG to package this back up and UPDATE the original table. I have something working for a single query, but I …
PostgreSQL parse countries in array against the countries table
We have content and country tables. Country is pretty simple: country_name column defined as string: Albania, Belgium, China, Denmark etc… Content is a table with half a million of rows with various data with countries column defined as array text[]. Each value there has a number of countries concatenat…
Split given string and prepare case statement
Table: table_name Insertion of records: Now I want to update set_name for some dates. For example: I want to update table like this: Note: The given_dates and set_name are pass a parameter because of they are dynamic. I may pass 2 sets as shown above s1,s2 or may pass 4 sets according to the requirement. So I…