Can TiDB use Json_table?

Translated
1

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

Original topic: tidb可以使用Json_table吗？

| username: Jjjjayson_zeng

[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5.1
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration] Go to TiDB Dashboard - Cluster Info - Hosts and take a screenshot of this page
[Attachment: Screenshot/Log/Monitoring]

image
image1194×714 82.4 KB

2
| username: Billmay表妹 | Original post link

Check out this link! JSON 函数 | PingCAP 文档中心

3
| username: 随缘天空 | Original post link

TiDB supports JSON functions. You can execute this on the client side, or refer to the link provided by the previous poster.

4
| username: 小龙虾爱大龙虾 | Original post link

This function is probably not supported yet :joy_cat:

5
| username: 小龙虾爱大龙虾 | Original post link

If the data is not much, you can process it with an application and then use CTE to construct this table.

6
| username: Jellybean | Original post link
  1. Tried to verify with v7.5.0, syntax is not yet compatible, and it will report an error.
mysql> select version();
+--------------------+
| version()          |
+--------------------+
| 8.0.11-TiDB-v7.5.0 |
+--------------------+
1 row in set (0.00 sec)

mysql>  SELECT * FROM JSON_TABLE ('[ {"c1": 1} ]', '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )) as jt;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your TiDB version for the right syntax to use line 1 column 27 near "('[ {"c1": 1} ]', '$[*]' COLUMNS( c1 INT PATH '$.c1' ERROR ON ERROR )) as jt"
  1. MySQL official documentation
    https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

Extracts data from a JSON document and returns it as a relational table having the specified columns.
One of the new JSON functions in MySQL 8.0 is JSON_TABLE.
It is also MySQL’s first table function.
That is, the return value is not a scalar value, but a result set.
JSON_TABLE will convert (part of) a JSON document into a relational table.

  1. Conclusion:
    JSON_TABLE is a function of MySQL 8.0, TiDB v6.5 probably does not support it yet, and it should be compatible in subsequent new versions.

You can verify it in your version, and you will likely reach a similar conclusion.
If your business heavily relies on it, you should bypass the JSON_TABLE function using other methods for now.

7
| username: 江湖故人 | Original post link

The performance will be terrible when associating more than a dozen tables in a distributed database.

8
| username: dba远航 | Original post link

The support is probably not very good.

9
| username: Miracle | Original post link

It depends on the user’s tolerance. As long as the user can accept it, associating twenty tables is not impossible :smirk:

10
| username: Jjjjayson_zeng | Original post link

Sorry, I can’t assist with that.

11
| username: 江湖故人 | Original post link

Redistributing data across various TiKV nodes or retrieving all data to the TiDB server for association both incur very high costs.

12
| username: ShawnYan | Original post link

It’s already in the planning stage and will be implemented later. You can follow this issue:

13
| username: system | Original post link

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