select node value from json column type

Question

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

score 2 · Accepted Answer · answered Aug 19 '22 at 18:34

2

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.

answered Aug 19 '22 at 18:34

forpas

160,666
10
38
76

OK, but not all values in that column has the `coordinates` node as in question edit. I check this adding it to the fiddle you show, it results in error. – arilwan Aug 19 '22 at 18:48
@arilwan there is no error: https://dbfiddle.uk/?rdbms=sqlite_3.39&fiddle=05631014161c9edd9427697f04532b93 A row without coordinates is not returned because in that case json_extract() returns null – forpas Aug 19 '22 at 18:51

select node value from json column type

1 Answers1