PostgreSQL中获取带变量名的JSON属性值及最佳实践咨询

一、获取weather JSON列中的icon值 #

根据你使用的数据库不同，查询语句会有差异，以下是几种主流数据库的实现方式：

MySQL #

假设你的表名为weather_data，需要获取的普通列比如id、created_at，结合JSON列weather中的icon值：

SELECT 
  id,
  created_at,
  weather->>'$.sessions.*.weather[0].icon' AS icon
FROM weather_data;

$.sessions.*用来匹配sessions下所有动态的时间戳属性，weather[0]取数组里第一个天气对象（你的示例中weather是单元素数组）。

PostgreSQL #

如果JSON列类型为jsonb（更推荐使用）：

SELECT 
  wd.id,
  wd.created_at,
  (jsonb_array_elements(s.session->'weather')->>'icon') AS icon
FROM weather_data wd,
     jsonb_each(wd.weather->'sessions') s(key, session);

通过jsonb_each展开sessions下的动态键，再解析weather数组里的icon字段。

SQL Server #

使用OPENJSON解析动态结构：

SELECT 
  wd.id,
  wd.created_at,
  weather_item.icon
FROM weather_data wd
CROSS APPLY OPENJSON(wd.weather, '$.sessions') AS sessions
CROSS APPLY OPENJSON(sessions.value, '$.weather')
WITH (
  icon NVARCHAR(10) '$.icon'
) AS weather_item;

二、关于时间戳作为属性名的最佳实践 #

不推荐把时间戳作为JSON对象的属性名，原因如下：

• 动态属性名会大幅增加查询、遍历的复杂度，你现在遇到的关联数据困难就是典型问题；
• 已经有dt字段存储相同时间戳，属于冗余存储，既浪费空间又可能导致数据不一致；
• 不符合JSON结构化设计原则，建议将sessions改为数组格式，示例如下：

{
  "lat":43.6423,
  "lon":-72.2518,
  "timezone":"America/New_York",
  "timezone_offset":-14400,
  "sessions":[
    {
      "dt":1651078174,
      "sunrise":1651052825,
      "sunset":1651103155,
      "temp":48.45,
      "weather":[
        {
          "id":804,
          "main":"Clouds",
          "description":"overcast clouds",
          "icon":"04d"
        }
      ]
    }
  ]
}

改成数组后，查询和关联数据会简单很多，比如MySQL中直接用$.sessions[*].weather[0].icon就能批量获取所有icon值。

内容的提问来源于stack exchange，提问作者Dani