我的資料庫中有三列
ID | boundary_coord | lat | lng |
------------------------------------------------------------
1 | [[27.913308738086336,78.0780080756358], | | |
| [21.913355835273993,75.07837828047921], | | |
| [26.91360564733089,32.07834574752177], | | |
| [23.9136475694797,35.07799837107608], | | |
| [34.913308738086336,34.0780080756358]] | | |
2 | [[16.966884703254756,79.30961584505829],| | |
| [17.967035001856917,73.30904577055392], | | |
| [13.967680163832473,74.30925753680006], | | |
| [15.967591705852122,71.30952320375857], | | |
| [12.96751655678886,77.30982165205582], | | |
| [14.966884703254756,72.30961584505829]] | | |
現在我正在嘗試從boundary_coord 中獲取第一個值，將其放在lat 列中，并從boundary_coord 中獲取第二個值，將其放在lng 列中
所以在Mysql中我寫了這個
SELECT SUBSTRING_INDEX(boundary_coord,',',2) AS LAT_LNG, farm_name FROM `farm_management_farm` WHERE boundary_coord is NOT Null
輸出
LAT_LNG
-------------------------------------------
[[27.913308738086336,78.0780080756358]
[[16.966884703254756,79.30961584505829]
顯然，這不是我所期望的輸出，而且還有一個問題是在開始時我得到了兩個空缺 [[
預期產出
ID | boundary_coord | lat | lng |
-----------------------------------------------------------------------------------
1 | [[27.913308738086336,78.0780080756358], |27.913308738086336|78.0780080756358 |
| [21.913355835273993,75.07837828047921], | | |
| [26.91360564733089,32.07834574752177], | | |
| [23.9136475694797,35.07799837107608], | | |
| [23.9136475694797,35.07799837107608]] | | |
2 | [[16.966884703254756,79.30961584505829],|16.966884703254756|79.30961584505829|
| [17.967035001856917,73.30904577055392], | | |
| [13.967680163832473,74.30925753680006], | | |
| [15.967591705852122,71.30952320375857], | | |
| [14.966884703254756,72.30961584505829], | | |
| [14.966884703254756,72.30961584505829]] | | |
uj5u.com熱心網友回復：
您可以像這樣JSON_EXTRACT決議boundary_coord中的陣列：
SELECT id, boundary_coord,
JSON_EXTRACT(boundary_coord, '$[0][0]') AS lat,
JSON_EXTRACT(boundary_coord, '$[0][1]') AS lng
FROM `farm_management_farm` WHERE boundary_coord IS NOT NULL;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/420923.html
標籤：
上一篇：未提交報告的串列