About JSON Parsing

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 关于Json解析

| username: TiDBer_rHsCTNhP

There is a JSON with a general format like
{“CAMERA_FL”: 81303, “LIDAR_FALCON”: 71349, “LIDAR_PROJECTED”: 71211}

I want to convert it to a table-like format with two columns named device and num, for example:
device num
CAMERA_FL 81303
LIDAR_PROJECTED 71211
LIDAR_FALCON 71349

How can I achieve this?

Thanks to everyone. I didn’t make it clear. The keys in the JSON are not fixed. The number of key-value pairs and the keys themselves are uncertain. That means the next JSON might have 100 pairs, and the keys might not be CAMERA_FL or LIDAR_FALCON. The keys need to be automatically adjusted.

| username: 我是咖啡哥 | Original post link

mysql> create table t_json(a json);
Query OK, 0 rows affected (1.04 sec)

mysql> 
mysql> insert into t_json(a) values('{"CAMERA_FL":81303, "LIDAR_FALCON":71349, "LIDAR_PROJECTED":71211}');
Query OK, 1 row affected (0.02 sec)

mysql> select * from t_json;
+-----------------------------------------------------------------------+
| a                                                                     |
+-----------------------------------------------------------------------+
| {"CAMERA_FL": 81303, "LIDAR_FALCON": 71349, "LIDAR_PROJECTED": 71211} |
+-----------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select JSON_EXTRACT(a, "$.CAMERA_FL"),JSON_EXTRACT(a, "$.LIDAR_FALCON"),JSON_EXTRACT(a, "$.LIDAR_PROJECTED") from t_json;
+--------------------------------+-----------------------------------+--------------------------------------+
| JSON_EXTRACT(a, "$.CAMERA_FL") | JSON_EXTRACT(a, "$.LIDAR_FALCON") | JSON_EXTRACT(a, "$.LIDAR_PROJECTED") |
+--------------------------------+-----------------------------------+--------------------------------------+
| 81303                          | 71349                             | 71211                                |
+--------------------------------+-----------------------------------+--------------------------------------+
1 row in set (0.00 sec)

mysql> select "CAMERA_FL" device ,JSON_EXTRACT(a, "$.CAMERA_FL") num from t_json
    -> union all
    -> select "LIDAR_FALCON" device ,JSON_EXTRACT(a, "$.LIDAR_FALCON") num from t_json
    -> union all
    -> select "LIDAR_PROJECTED" device ,JSON_EXTRACT(a, "$.LIDAR_PROJECTED") num from t_json;
+-----------------+-------+
| device          | num   |
+-----------------+-------+
| LIDAR_PROJECTED | 71211 |
| LIDAR_FALCON    | 71349 |
| CAMERA_FL       | 81303 |
+-----------------+-------+
3 rows in set (0.01 sec)
| username: linnana | Original post link

The query efficiency of the JSON function is acceptable.

| username: 我是咖啡哥 | Original post link

I haven’t used it in production, not really sure :joy:

| username: tidb菜鸟一只 | Original post link

The efficiency of the database’s JSON processing functions is very poor, it is recommended to implement it in the application.

| username: redgame | Original post link

Similarly, I suggest using the application.

| username: Anna | Original post link

The efficiency of the database’s JSON processing functions is very poor, I recommend implementing it in the application.

| username: TiDBer_rHsCTNhP | Original post link

This key is not fixed. That is to say, in this JSON, there are three keys: CAMERA_FL, LIDAR_FALCON, and LIDAR_PROJECTED. However, in the next JSON, there might be 100 keys, and they could be 100 different values.

| username: jansu-dev | Original post link

It’s better to handle it in a Python program; there are more convenient functions available.

| username: cassblanca | Original post link

The number of keys cannot be fixed, so it is better to use a language like Python to traverse, extract the necessary information, and then save it to the database. It is quite challenging at the database level.

| username: zhanggame1 | Original post link

It won’t work if the quantity is not fixed, right?

| username: zhanggame1 | Original post link

If you can use the MySQL JSON_TABLE function, you can get it done. TiDB currently does not support this.

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.