I have query: The result of this query duplicates the same image urls in column urlLinks: How can I leave as a result only unique image urls? GROUP BY cannot be removed from the request!!! Answer JSON_ARRAYAGG() does not support DISTINCT. You can SELECT DISTINCT in a subquery, and then aggregate: Demo on DB F…
Tag: arrays
LIMIT and Contains in a single JSONB Query
I have 2 queries that separately run perfectly: This one limits the results: SELECT jsonb_agg(elem) as data FROM ( SELECT * FROM file_data, jsonb_array_elements(file_data) a(elem) LIMIT 3 ) …
Find rows based on nested key in jsonb array
I have a jsonb column in Postgres 9.6 that contains JSON arrays of the following form: [ { “courses”: { “course-1”: { “graduated”: false }, “course-5”: { “graduated”: true } } }, { “…
SQL how to convert array to values for use in IN clause
Working in Postgres SQL: My query argument comes in as a comma separated string: ‘ABC,BCD’ I am trying to construct the query below, but getting an error: ERROR: Operator does not exist: character varying = text[] My question is how to convert ‘ABC,BCD’ to a list of values to use in th…
JSONB sort aggregation
I found this query that suits my needs thanks to this answer here in order to sort fields of data in a JSON document. (Fake, generated random data) SELECT jsonb_agg(elem) FROM ( SELECT * FROM …
In Hive, how to convert an array of string to an array of numeric numbers
I have a hive table that looks like the following: I want the result to be the following: I need to convert them into an array of float so that I can use them in ST_Constains(ST_MultiPolygon(), st_point()) to determine if a point is in an area. I am new to Hive, not sure if that is possible, any help would
Query JSONB column for any value where =?
I have a jsonb column which has the unfortunate case of being very unpredictable, in some cases its value may be an array with nested values: [“UserMailer”, “applicant_setup_3”, [“5cbffeb7-8d5e-4b52-…
Expand a postgres array of JSON within a single column into multiple records
I have a table called goals where each row contains 1 goal. The data about specific metrics for the goal is stored in ARRAY of JSON called goal_metrics. Sometimes there is 1 metric, sometimes there …
BigQuery nested table UPDATE based on condition of non-nested data AND nested data
I am trying to update records in a bigQuery database that looks like this: Using the code below: UPDATE `tottus-chile.espacio.nested_table` SET addresses = ARRAY( SELECT AS STRUCT * REPLACE(‘…
Array operation on hive collect_set
I am working on hive on large dataset, I have table with colum array and the content of the colum is as follows. [“20190302Prod4” “20190303Prod1” “20190303Prod4” “20190304Prod4” “20190305Prod3” “…