mysql - 如何选择以文本形式存储的 JSON 数据

Question

我必须从 MariaDB 数据库中提取数据，该数据库的所有者已将 JSON 数据存储在 varchar 字段中，格式如下:

   [-100, -18.3, -10.1, 2.2, 5.8, ...]

我希望能够从每个 JSON 编码文本字段中选择单独的条目。

我一直在阅读有关 MariaDB 中 JSON 支持的许多功能，并且查看了许多有关如何将数据作为 JSON 存储在文本字段中的示例，但它们都需要更改数据的插入方式和/或架构。

我无法以任何方式更改数据库。我有只读访问权限。

数据库的所有者目前正在使用 MariaDB 10.0，但我也许可以让他们升级到 10.1

简而言之，鉴于以下(非常简单的示例)，我如何选择“数据”字段中的第二个元素？

我认为使用 JSON 功能是可行的方法(假设所有数据都是 JSON)，但是还有其他方法吗？性能并不是那么重要。

MariaDB [mtest]> show columns from cal from mtest;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| data  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.00 sec)

MariaDB [mtest]> select * from cal;
+---------+
| data    |
+---------+
| [10.1,12.0,16.78,18.9] |
+---------+
1 row in set (0.00 sec)

Answer 1

如果您可以通过CONNECT升级到10.1(从MariaDB 10.1.9)可以使用JsonGet_Real功能。

尝试:

MariaDB [_]> SELECT VERSION();
+-----------------+
| VERSION()       |
+-----------------+
| 10.1.14-MariaDB |
+-----------------+
1 row in set (0.00 sec)

MariaDB [_]> INSTALL SONAME 'ha_connect';
Query OK, 0 rows affected (0.01 sec)

MariaDB [_]> CREATE FUNCTION `jsonget_real` RETURNS REAL SONAME 'ha_connect.so';
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> DROP TABLE IF EXISTS `cal`;
Query OK, 0 rows affected, 1 warning (0.01 sec)

MariaDB [_]> CREATE TABLE IF NOT EXISTS `cal` (
    ->   `data` VARCHAR(255)
    -> );
Query OK, 0 rows affected (0.00 sec)

MariaDB [_]> INSERT INTO `cal`
    ->   (`data`)
    -> VALUES
    ->   ('[10.1,12.0,16.78,18.9]');
Query OK, 1 row affected (0.00 sec)

MariaDB [_]> SELECT `data` FROM `cal`;
+------------------------+
| data                   |
+------------------------+
| [10.1,12.0,16.78,18.9] |
+------------------------+
1 row in set (0.00 sec)

MariaDB [_]> SELECT `jsonget_real`(`data`, '[1]', 2) FROM `cal`;
+--------------------------------+
| jsonget_real(`data`, '[1]', 2) |
+--------------------------------+
|                          12.00 |
+--------------------------------+
1 row in set (0.00 sec)