Data Analysis Using TiDB

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

Original topic: 使用tidb进行数据分析

| username: emerson_cai

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed when the issue occurred
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

There is an instance table with a field_info column, which is a JSON field. Each JSON is a map that defines some attributes of this instance: attribute values, each row is different. Now I want to classify instances according to field_info. For example, what values does the business attribute have among all instances, and what values does os_name have among instances where business = “game”. Is there a good solution for this using TiDB?

Currently using the following query:
SELECT DISTINCT JSON_EXTRACT(field_info, CONCAT(‘$’, ‘.’, ‘handsome’)) FROM instance WHERE (model_id = 576460752303423531 AND name = “test-589” ) AND JSON_CONTAINS(JSON_KEYS(field_info), JSON_ARRAY(‘handsome’)) != 0
When the data volume is large, it consumes a lot of time, and the fields used for analysis are uncertain, making it inconvenient to create indexes.

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

Developers who store JSON fields in the database should be directly reprimanded. It may save them trouble, but it causes issues for others. Currently, the handling of JSON fields is relatively poor across various databases, and TiDB doesn’t have any good solutions either.

| username: system | Original post link

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