Want to remove nulls from an array in hive/sql for example : array is [‘1’,null] after converting to string values it should be ‘1’ only. to split the array I am using below: concat_ws( “,”, …
Tag: arrays
PostgreSQL json_array_elements with array indexes (keys)
Simple query works fine: But I also want to retrieve array keys somehow so the output would be like: UPD Seems like row_number() is a p̶r̶o̶p̶e̶r̶ solution, but I cannot figure out how to use it further. Lets say i have ‘posts’ table, each post contains an array of related comments in JSON format: The goal is to expand not
Finding a single element in postgres json array
I have a table that has a Json typed column and I am trying query that table based of values that are contained in that json column. Here is the relevant DDL: Here is what a roles value would look like: I want to be able to perform a query like this: So I’ve tried this: And it is giving
Union of arrays as aggregate function
I have the following input: I want the following output: I am grouping by name. For each group, the count should be aggregated as the max and the options should be aggregated as the union. I am having troubles figuring out how do the the latter. Currently, I have this query: http://rextester.com/YTZ45626 I know this can be easily done by
How to run SQL queries in a loop
How can I run this SQL query multiple times in a loop, where I replace the word ‘pubs’ with another word during each iteration. Is there a way to store an array of strings and loop through them? Answer In general, it’s usually better performance-wise to do bulk or batch queries than queries in a loop, since you can save
Check if NULL exists in Postgres array
Similar to this question, how can I find if a NULL value exists in an array? Here are some attempts. Only a trick with array_to_string shows the expected value. Is there a better way to test this? Answer Postgres 9.5 or later Or use array_position(). Basically: See demo below. Postgres 9.3 or later You can test with the built-in functions
Replace [ ] bracket in a string
I have a string that includes brackets, [], around a number. Since this string represents my column names for a SQL database I need to remove/replace them. So far I do it in the following way: It works fine, but it looks ugly for me since I have to do that for [1] to [20]. Is there a way to
How to merge all integer arrays from all records into single array in postgres
I have a column which is of type integer array. How can I merge all of them into a single integer array? For example: If I execute query: I get result set as: How can I get {1,2,3,4,5} as final result? Answer You could use unnest to open up the arrays and then array_agg to put them back together:
Compare items in a SQL column with array
I have a SQL server table where I want to query for items on rows which have a value equal to an item in Array. Here’s my example: And my array: So, I want to return from SQL rows matching items in the array. my SQL query should return items where column1 matches an item in the array: This all
mysql selecting multiple rows that match array of ids
First of all: SELECT `key`, `value` FROM settings WHERE `key` = :base_url OR `key` = :google_analytics OR `key` = :site_domain Is this correct way of selecting multiple rows? Or is there a …