MySQL将第一个和第二个元素从一列拆分为不同的列

Question

我的数据库中有三列

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

Answer 1

您可以使用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;