Find max value from column that has a json object with key-value pairs

user_10231056 0
Viewed 1284

I Have a table that has a column of a JSON string (key-value pairs) of items, I want to return only the key-value pair of the largest value

I can do this by first UNNESTing the JSON object and then taking the largest value by ORDER BY item, value (DESC) and using array_agg to get the largest one. The problem is that this means creating multiple tables and is slow. I am hoping that in one operation, I'll be able to extract the largest key-value pair.

This:

| id | items                              |
| -- | ---------------------------------- |
|  1 | {Item1=7.3, Item2=1.3, Item3=9.8}  |
|  2 | {Item2=4.4, Item3=5.2, Item1=0.1}  |
|  3 | {Item5=6.6, Item2=1.4, Item4=1.5}  |
|  4 | {Item6=0.9, Item7=11.2, Item4=8.1} |

Should become:

| id | item  | value |
| -- | ----- | ----- |
|  1 | Item3 |  9.8  |
|  2 | Item3 |  5.2  |
|  3 | Item5 |  6.6  |
|  4 | Item7 |  11.2 |

I don't actually need the value, so long as the item is the largest from the JSON object, so the following would be fine as well:

| id | item  |
| -- | ----- |
|  1 | Item3 |
|  2 | Item3 |
|  3 | Item5 |
|  4 | Item7 |
presto greatest-n-per-group tsql sql json
2 Answers

Presto's UNNEST performance got improved in Presto 316. However, you don't need UNNEST in this case.

You can

Use query like

SELECT 
    id,
    reduce(
        -- conver JSON to array of key/value pairs
        map_entries(CAST(data AS map(varchar, double))),
        -- initial state for reduce (must be same type as key/value pairs)
        (CAST(NULL AS varchar), -1e0), -- assuming your values cannot be negative
        -- reduction function
        (state, element) -> if(state[2] > element[2], state, element),
        -- reduce output function
        state -> state[1]
    ) AS top
FROM (VALUES
    (1, JSON '{"Item1":7.3, "Item2":1.3, "Item3":9.8}'),
    (4, JSON '{"Item6":0.9, "Item7":11.2, "Item4":8.1}'),
    (5, JSON '{}'),
    (6, NULL)
) t(id, data);

Output

 id |  top
----+-------
  1 | Item3
  4 | Item7
  5 | NULL
  6 | NULL
(4 rows)
user_654580

Store the values one per row in a child table.

CREATE TABLE child (
 id INT NOT NULL,
 item VARCHAR(6) NOT NULL,
 value DECIMAL(9,1),
 PRIMARY KEY (id, item)
);

You don't have to do a join to find the largest per group, just use a window function:

WITH cte AS (
  SELECT id, item, ROW_NUMBER() OVER (PARTITION BY id ORDER BY value DESC) AS rownum
  FROM mytable
)
SELECT * FROM cte WHERE rownum = 1;

Solving this with JSON is a bad idea. It makes your table denormalized, it makes the queries harder to design, and I predict it will make the query performance worse.

Bill_Karwin_208600
Related