如何在PostgreSQL中筛选JSONB列含type为Longitude的行？

我在Next.js项目中使用@vercel/postgres，能成功从PostgreSQL数据库查询行，但想筛选出JSONB列中值为Longitude（不是键）的行。

表结构 #

CREATE TABLE IF NOT EXISTS events (
      id SERIAL PRIMARY KEY,
      json JSONB,
      "at" TIMESTAMP WITH TIME ZONE,
      "createdAt" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

当前查询语句 #

SELECT
     id,
     at,
     json -> 'uplink_message' -> 'decoded_payload' -> 'messages' -> 0 AS message
FROM events 
-- WHERE ("json" -> 'uplink_message' -> 'decoded_payload' -> 'messages' -> 0)::jsonb ? 'Longitude' 
ORDER BY at 
DESC LIMIT ${limit}

被注释的WHERE子句无法筛选出任何行，目标行的JSON结构示例：

{
  "id": 2052,
  "at": "2024-02-14T15:03:15.000Z",
  "json": {
    "received_at": "2024-02-14T15:03:17.926885957Z",
    "uplink_message": {
      "decoded_payload": {
        "messages": [
          [
            {
              "type": "Longitude",
              "measurementId": "4197",
              "measurementValue": "-1.40000"
            },
            {
              "type": "Latitude",
              "measurementId": "4198",
              "measurementValue": 50.9000
            },
            {
              "type": "Battery",
              "measurementId": "3000",
              "measurementValue": 94
            }
          ]
        ]
      }
    }
  }
}

解决方案 #

错误分析 #

1. 操作符使用错误：?操作符的作用是检查JSONB对象是否包含指定键，但你需要匹配的是type字段的值为Longitude，两者逻辑完全不同。
2. 路径嵌套处理错误：messages的结构是数组套数组（messages[0]是一个子数组，里面才是包含type的对象），原查询只取了外层数组的第一个元素，没深入到内层对象。

正确的WHERE子句写法 #

方法1：使用jsonb_path_exists（路径清晰，适合复杂JSON结构）

WHERE jsonb_path_exists(
  "json",
  '$.uplink_message.decoded_payload.messages[*][*] ? (@.type == "Longitude")'
)

• 路径表达式说明：[*]表示遍历数组中的所有元素，先遍历messages下的所有子数组，再遍历每个子数组里的所有对象，检查是否存在type等于Longitude的项。

方法2：使用数组展开函数筛选

WHERE EXISTS (
  SELECT 1
  FROM jsonb_array_elements("json" -> 'uplink_message' -> 'decoded_payload' -> 'messages') AS outer_arr
  CROSS JOIN jsonb_array_elements(outer_arr) AS inner_obj
  WHERE inner_obj ->> 'type' = 'Longitude'
)

• 逻辑说明：先通过jsonb_array_elements展开messages外层数组，再展开每个子数组，最后检查每个对象的type值是否为Longitude，只要存在符合条件的对象就保留该行。

完整查询示例 #

SELECT
     id,
     at,
     json -> 'uplink_message' -> 'decoded_payload' -> 'messages' -> 0 AS message
FROM events 
WHERE jsonb_path_exists(
  "json",
  '$.uplink_message.decoded_payload.messages[*][*] ? (@.type == "Longitude")'
)
ORDER BY at 
DESC LIMIT ${limit}

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