select node value from json column type-CodePudding

A table I called raw_data with three columns: ID, timestamp, payload, the column paylod is a json type having values such as:

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

From this I want to select rows where the coordinates is available, such as [-0.1094,51.5141]in this case.

SELECT * 
FROM raw_data, json_each(payload) 
WHERE json_extract(json_each.value, '$.data.geo.') IS NOT NULL 
LIMIT 20;

Nothing was returned.

EDIT

NOT ALL json objects have the coordinates node. For example this value:

{
  "data": {
    "author_id": "1556031969062010881",
    "created_at": "2022-08-18T01:42:21.000Z",
    "geo": {
      "place_id": "006c6743642cb09c"
    },
    "id": "1560079621017796609",
    "text": "Dear Desperate sister  say husband no dey oo."
  },
  "matching_rules": [
    {
      "id": "1560077018183630848",
      "tag": "kaduna-kano-katsina-dutse-zaria"
    }
  ]
}

CodePudding user response：

The correct path is '$.data.geo.coordinates.coordinates' and there is no need for json_each():

SELECT * 
FROM raw_data
WHERE json_extract(payload, '$.data.geo.coordinates.coordinates') IS NOT NULL;

See the demo.