使用substr、instr和trim函数隔离字符串数据SQLite

Question

如何从下面的数据字符串中分离出纬度和经度坐标。我尝试了substr、instr和trim的几种组合，但都没有成功。提前感谢您的帮助。

{"latitude":31.3495768,"longitude":-109.5176988,"accuracy":25.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3495769,"longitude":-109.5176986,"accuracy":25.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.349555,"longitude":-109.5176636,"accuracy":55.0,"altitude":0.0,"heading":0.0,"speed":0.0}   
{"latitude":31.3495556,"longitude":-109.5176675,"accuracy":55.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3495556,"longitude":-109.5176675,"accuracy":55.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3495768,"longitude":-109.5176988,"accuracy":25.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.349577,"longitude":-109.5176988,"accuracy":25.0,"altitude":0.0,"heading":0.0,"speed":0.0}   
{"latitude":31.349577,"longitude":-109.5176988,"accuracy":25.0,"altitude":0.0,"heading":0.0,"speed":0.0}   
{"latitude":31.3495545,"longitude":-109.5176644,"accuracy":55.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3091678,"longitude":-109.5241938,"accuracy":25.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3091684,"longitude":-109.5241915,"accuracy":46.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3091677,"longitude":-109.524194,"accuracy":24.0,"altitude":0.0,"heading":0.0,"speed":0.0}   
{"latitude":31.3091677,"longitude":-109.5241939,"accuracy":24.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3091676,"longitude":-109.5241938,"accuracy":25.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":33.415518729047,"longitude":-111.93584326665,"accuracy":100.0}                                 
{"latitude":31.3495769,"longitude":-109.5176986,"accuracy":25.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3412066,"longitude":-109.5530025,"accuracy":50.0,"altitude":0.0,"heading":0.0,"speed":0.0}  
{"latitude":31.3411891,"longitude":-109.5530071,"accuracy":51.0,"altitude":0.0,"heading":0.0,"speed":0.0}

Answer 1

coordinates列包含格式良好的JSON的值，因此可以使用json_extract()函数：

SELECT json_extract(coordinates, '$.latitude') Latitude,
       json_extract(coordinates, '$.longitude') Longtitude
FROM messages 
WHERE coordinates IS NOT NULL;

请参阅demo。