SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE raw_data (ID, timestamp, payload)
✓
INSERT INTO raw_data VALUES
(1, 1234567890123,
'{
  "data": {
    "author_id": "1461871206425108480",
    "created_at": "2022-08-17T23:19:14.000Z",
    "geo": {
      "coordinates": {
        "type": "Point",
        "coordinates": [
          -0.1094,
          51.5141
        ]
      },
      "place_id": "3eb2c704fe8a50cb"
    },
    "id": "1560043605762392066",
    "text": " ALWAYS @ London, United Kingdom"
  },
  "matching_rules": [
    {
      "id": "1560042248007458817",
      "tag": "london-paris"
    }
  ]
}'
),
(2, 1234567890124,
'{
  "data": {
    "author_id": "1461871206425108480",
    "created_at": "2022-08-17T23:19:14.000Z",
    "geo": {
      "coordinates": {
        "type": "Point",
        "coordinates": null
      },
      "place_id": "3eb2c704fe8a50cb"
    },
    "id": "1560043605762392066",
    "text": " ALWAYS @ London, United Kingdom"
  },
  "matching_rules": [
    {
      "id": "1560042248007458817",
      "tag": "london-paris"
    }
  ]
}'
)
✓
SELECT * 
FROM raw_data
WHERE json_extract(payload, '$.data.geo.coordinates.coordinates') IS NOT NULL;


ID
timestamp
payload


1
1234567890123
{
  "data": {
    "author_id": "1461871206425108480",
    "created_at": "2022-08-17T23:19:14.000Z",
    "geo": {
      "coordinates": {
        "type": "Point",
        "coordinates": [
          -0.1094,
          51.5141
        ]
      },
      "place_id": "3eb2c704fe8a50cb"
    },
    "id": "1560043605762392066",
    "text": " ALWAYS @ London, United Kingdom"
  },
  "matching_rules": [
    {
      "id": "1560042248007458817",
      "tag": "london-paris"
    }
  ]
}