从具有键值对的JSON对象的列中找到最大值

Question

i有一个表，该表具有json字符串(键值对(项目的列，我只想返回最大值的键值对

我可以先通过UNNEST将JSON对象做到这一点，然后将最大的值乘以ORDER BY item, value (DESC)并使用array_agg获取最大的值。问题在于，这意味着创建多个表，并且很慢。我希望在一次操作中，我能够提取最大的键值对。

这个：

| 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} |

应该成为：

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

我实际上并不需要该值，只要该项目是JSON对象的最大值，以下内容也很好：

| id | item  |
| -- | ----- |
|  1 | Item3 |
|  2 | Item3 |
|  3 | Item5 |
|  4 | Item7 |

Answer 1

Presto的UNNEST性能在Presto 316中得到了改进。但是，在这种情况下，您不需要UNNEST。

你可以

• 使用JSON CAST和map_entries
将您的JSON转换为键/价值对的ARARY
• reduce选中键的数组以获取最高值
  • 由于键/值对表示为匿名row元素，因此使用订阅操作员使用对row元素的位置访问非常方便(自Presto 314以来可用(

使用查询

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);

输出

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

Answer 2

将每个行的值存储在子表中。

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

您不必加入即可找到每组最大的，只需使用一个窗口函数：

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;

用JSON解决这个问题是一个坏主意。它使您的表不符合您的表格，使查询更难设计，我预计它将使查询性能变得更糟。