Postgres-获取从JSON创建的最大列

Question

我在Postgres数据库中有一个JSON列，它有星期的日期键和该日期的访问值。我想得到天数的最大值，并将其作为一列添加到一周中所有天数的值旁边。

我有下面的查询，我认为可以做到。但我目前收到错误"列‘星期一’不存在">

为什么从JSON中提取星期一之后就不存在了？内部查询运行良好，并返回所有日期和相应的值，正如我所期望的那样(列为星期一、星期二、星期三、星期四、星期五(。我认为查询这些列并获得最大值会相对容易。

SELECT Monday, Tuesday, Wednesday, Thursday, Friday,
GREATEST(Monday, Tuesday, Wednesday, Thursday, Friday) as MaxDay
FROM "VisitData"
WHERE
(SELECT 
popularity_by_day ->> 'Monday' AS Monday,
popularity_by_day ->> 'Tuesday' AS Tuesday,
popularity_by_day ->> 'Wednesday' AS Wednesday,
popularity_by_day ->> 'Thursday' AS Thursday,
popularity_by_day ->> 'Friday' AS Friday
FROM "VisitData")

更新：

在别名子查询之后，我现在有了这个查询：

SELECT Monday, Tuesday, Wednesday, Thursday, Friday,
GREATEST(Monday, Tuesday, Wednesday, Thursday, Friday) as MaxDay
FROM
(SELECT 
popularity_by_day ->> 'Monday' AS Monday,
popularity_by_day ->> 'Tuesday' AS Tuesday,
popularity_by_day ->> 'Wednesday' AS Wednesday,
popularity_by_day ->> 'Thursday' AS Thursday,
popularity_by_day ->> 'Friday' AS Friday
FROM "VisitData") AS visits
```
This gives me the structure of the output im expecting but the results are inconsistent.
For example row 1 in the result
Monday = 5
Tuesday = 89
Wednesday = 95
Thursday = 120
Friday = 102
MaxDay = 95  (why not 120)
And for row 2
Monday = 329
Tuesday = 350
Wednesday = 439
Thursday = 397
Friday = 314
MaxDay = 439 (This is correct)

Answer 1

我想你想要一个横向连接：

SELECT v.Monday, v.Tuesday, v.Wednesday, v.Thursday, v.Friday,
GREATEST(v.Monday, v.Tuesday, v.Wednesday, v.Thursday, v.Friday) as MaxDay
FROM "VisitData" vd CROSS JOIN LATERAL
(SELECT vd.popularity_by_day ->> 'Monday' AS Monday,
vd.popularity_by_day ->> 'Tuesday' AS Tuesday,
vd.popularity_by_day ->> 'Wednesday' AS Wednesday,
vd.popularity_by_day ->> 'Thursday' AS Thursday,
vd.popularity_by_day ->> 'Friday' AS Friday
) v;