I am facing issue while filtering data with array I have columns userid,event_name,attributes,ti Attributes column have value like this {"bool_sample":true,"array_int":[10,20,25,38],"array_string":["hello","world","i am fine"]} My query SELECT * FROM "events-data" CROSS JOIN UNNEST(CAST(json_extract(attributes, '$["array_int"]') AS array<int>)) AS t(val) WHERE val > 9;…
I have a dataset looks like this: Link_number Houband Time Mean_speed Sample_Number Link 1 8 8:00 52 2 Link 1 8 8:30 55 5 Link 2 9 9:00 20 3 Link 2 9 9:30 40 4 I need to do…
I am trying to replace the value of one of the columns in the SELECT result to 0. Normally I can do: SELECT 0 AS column_to_set, other_col_1, other_col_2, ... FROM tbl However, there are lots of "other_cols" in the source…
Using Presto SQL, the original format of the column appears like this "{"ORDERS (completed)": 1000.00, "ORDERS (failed) USD": 3000.00}" and I am trying to extract the key and value by parsing and casting as map (varchar, double) and then unnesting…
I have a column called users with this JSON. { "[email protected]":[1.0,5.95], "[email protected]":[2.0,30.733], "[email protected]":[1.0,4.433], "[email protected]":[2.0,16.25] } I want to unwrap it in this way splitUsers firstValue secondValue [email protected] 1.0 5.95 [email protected] 2.0 30.733 [email protected] 1.2 4.433 [email protected] 2.0 16.25 I managed…
My database has a column that contains a JSON array like this: WITH dataset AS ( SELECT * FROM (VALUES ('1', JSON'[{ "name" : "foo" }, { "name" : "bar" }]'), ('2', JSON'[{ "name" : "fizz" }, { "name" :…
I'm trying to find a explode_percent column by dividing a column to sum of the bucket. Below is my query - SELECT amount, sum(amount) over ( partition by col1, col2, col3, col4 order by col2, col3, col4 ) as explode_amount,…
I have the following table_1 which shows the time each person in Room_A. person start_time end_time John 5 16 Mary 7 10 Peter 8 12 Then I want to have an output table which shows the time and the people…
I have a following table : prod_name - quantity - group tv - 30 - base microwave - 10 - base watch - 5 - base phone - 25 - base washer - 15 - base dryer. - 14 -…
I have two string formatted columns in my table. One of the columns has json objects in it. The keys in the JSON objects are not fixed - so the problem is I cant use standard json extract functions. Here…