如何在PostgreSQL中筛选JSONB列含指定嵌套对象的数组行？

问题 #

我在Next.js项目里用@vercel/postgres能正常查PostgreSQL数据，但得筛选出JSONB列里包含Longitude或Latitude类型值的行。表结构如下：

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  "at" TIMESTAMP WITH TIME ZONE,
  json JSONB
)

当前的查询语句得手动指定嵌套数组的前三个索引才能筛选，没法覆盖所有可能的数组元素：

SELECT
  id,
  "at",
  (json -> 'uplink_message' -> 'decoded_payload' -> 'messages' -> 0) AS data
FROM
  events
WHERE
  json -> 'uplink_message' -> 'decoded_payload' -> 'messages' -> 0 -> 0 ->> 'type' IN ('Latitude', 'Longitude')
  OR json -> 'uplink_message' -> 'decoded_payload' -> 'messages' -> 0 -> 1 ->> 'type' IN ('Latitude', 'Longitude')
  OR json -> 'uplink_message' -> 'decoded_payload' -> 'messages' -> 0 -> 2 ->> 'type' IN ('Latitude', 'Longitude')
ORDER BY
  "at" DESC
LIMIT
  ${limit}

需要优化WHERE子句，不用手动写数组索引，确保能匹配所有带Latitude或Longitude类型的嵌套对象。

优化方案 #

给你两种不用手动指定索引的实现方式，按需选：

方案1：用jsonb_path_exists（推荐，写法简洁） #

用JSONPath表达式遍历所有嵌套数组元素，检查是否存在符合条件的对象：

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

• $.uplink_message.decoded_payload.messages[*][*]：遍历messages下所有二级数组的元素（因为messages是数组套数组）
• type ? (@ == "Latitude" || @ == "Longitude")：检查元素的type字段是不是目标值

方案2：用unnest展开数组 #

通过jsonb_array_elements逐层展开嵌套数组，再筛选符合条件的行：

SELECT DISTINCT
  e.id,
  e."at",
  (e.json -> 'uplink_message' -> 'decoded_payload' -> 'messages' -> 0) AS data
FROM
  events e
JOIN LATERAL jsonb_array_elements(e.json -> 'uplink_message' -> 'decoded_payload' -> 'messages') AS msg_arr
  ON true
JOIN LATERAL jsonb_array_elements(msg_arr) AS msg_obj
  ON msg_obj ->> 'type' IN ('Latitude', 'Longitude')
ORDER BY
  e."at" DESC
LIMIT
  ${limit}

• 第一次jsonb_array_elements把messages数组展开，得到内层的数组元素
• 第二次jsonb_array_elements把内层数组展开，得到每个对象
• 用JOIN筛选出带目标type的对象，DISTINCT用来去重（同一行可能有多个符合条件的对象）

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