How can I select only the rows with a JSON column containing an array where at least one object matches in PostgreSQL?

Question

I'm using @vercel/postgres in a Next.js project.

I can successfully select rows from a PostgreSQL database, but want to only select those rows whose JSONB column contain a deeply nested value (not key) of Longitude or Latitude.

The table is simple:

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

My query is:

    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}

but the WHERE clause is manually names the first three objects in the array of (array of) data.

I'm new to PostgreSQL, so please forgive any mistakes, and please do suggest possible improvements.

An example of a row I'd like to select is:

{
  "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
            }
          ]
        ]
      }
    }
  }
}

(others don't have the lat/long objects)

Answer 1

You may convert the array of objects to recordset and search if a qualifying record exists in the where clause.

select * from events 
where exists 
(
 select 
 from jsonb_to_recordset("json"->'json'->'uplink_message'->'decoded_payload'->'messages'->0)
      as t("type" text) 
 where t."type" in ('Latitude', 'Longitude')
);

DB-fiddle demo

You can use jsonb_array_elements instead of jsonb_to_recordset to the same effect.

select * from events where exists 
(
 select 
 from jsonb_array_elements("json"->'json'->'uplink_message'->'decoded_payload'->'messages'->0) j 
 where j->>'type' in ('Latitude', 'Longitude')
)

You may consider jsonpath tools too.
Unrelated but using reserved words for names is hardly a good idea.