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…
If I have a field jsonCol which has list of json objects, for example: [{'name': 'fieldA', 'enum': 'someValA'}, {'name': 'fieldB', 'enum': 'someValB'}, {'name': 'fieldC', 'enum': 'someValC'}] Another row may look like: [{'name': 'fieldA', 'enum': 'someValA'}, {'name': 'fieldC', 'enum': 'someValC'}] How…
How can I insert a row per group member with a constant value? For example, the following table is a result of ranking hospital visits per user, from earliest to latest. name time lifetime_visit_at_hospital jack 2022-12-02 03:25:00.000 1 jack 2022-12-02…