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 joins, effectively generating many
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 aggregate them in the array: for this, you can use
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 want to do an
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 concatenated like: {“denmark,finland,france,germany,ireland,gb,italy,netherlands,poland,russia,spain,sweden,australia,brazil,canada,china,india,indonesia,japan,malaysia,vietnam,mexico,”south korea”,thailand,usa,singapore,uae”} The update from internal team is for a
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 need the dynamic