Call For Participation: Improve the JSON functionality

[qw4990]

All issues are open and welcomed to contributors. You can join #sig-exec on tidb community slack to discuss and get help from someone.

Feature Request

Is your feature request related to a problem? Please describe:

Here we collect some issues about JSON functions, please help us to fix them if you are interested in any of them:

• Implement the unsupported JSON function JSON_MERGE_PATCH; difficulty: medium
• Implement the unsupported JSON function JSON_PRETTY; difficulty: medium
• 7623 Implement the unsupported JSON function JSON_ARRAYAGG; difficulty: medium executor: add builtin aggregate function json_arrayagg #19957
• 7623 Implement the unsupported JSON function JSON_OBJECTAGG; difficulty: medium; @hg2990656 *: add builtin aggregate function json_objectagg #11154
• 14488 Implement the unsupported JSON function JSON_STORAGE_SIZE ; difficulty: medium
• 12178 JSON_EXTRACT gets wrong decimal when insert into table; difficulty: easy
• 13722 Invalid JSON value for CAST to DATE/TIME/DATETIME/TIMESTAMP from column json_extract for virtual column; difficulty: easy
• 9995 JSON_OBJECT handle long strings wrongly; difficulty: easy
• 10460 Compare json objects wrongly; difficulty: easy
• 10461 Compare json floats wrongly; difficulty: easy
• 11386 Loss accuracy when inserting json records; difficulty: easy
• 9988 Casting times to json cannot be compatible with MySQL; difficulty: easy
• 10390 Casting strings to json cannot be compatible with MySQL; difficulty: easy
• 12233 JSON_EXTRACT fails to cast as bool; difficulty: easy
• 11489 Convert json to uint different from mysql; difficulty: easy
• 10467 Incorrect GROUP BY for JSON values; difficulty: easy
• 13710 Incorrect results when processing JSON_EXTRACT(...) IN (...); difficulty: easy

Thanks for your help! If you have any question, please discuss it with us in our slack channel #sig-exec on tidb community slack or in this issue by comments :D

Document Collection

• Proposal doc: (follow reference implementation in MySQL)
• Weekly report: TODO

Talent Challenge Program information

• Mentor of this issue: @morgo
• Recommended skills: Golang, MySQL
• Estimated Workloads: 1 month

Milestones and action items

Milestone 1: Merge missing functions from 5.7.

• JSON_MERGE_PATCH
• JSON_ARRAYAGG

Milestone 2: Fix bugs in current implementation

• JSON_OBJECT handle long strings wrongly
• Casting times to json cannot be compatible with MySQL

Milestone 3: Implement functionality from MySQL 8.0

• JSON_TABLE function
• JSON_VALUE function
• Multi-valued indexes (member of function)

* The MySQL 8.0 functionality may need to be broken down into design docs, since TiDB does not currently support TABLE functions or multi-valued indexes. There may be some assumptions that we've made that will need to be fixed for these to be added.

[YiuTerran]

Please watch Multi-Valued Indexes introduced by MySQL 8.0.17, it's quite usefull.

Implementation of member of should be considered.

[xxchan]

Hi, what's the status of this issue?

[morgo]

Hi, what's the status of this issue?

@xxchan We have someone working on the casting behavior, but nobody is yet looking at Milestone 3. These are harder tasks, if you would like to take a look at one, please reach out to me on Slack (Morgan Tocker). I would be happy to talk you through the issues.

[dveeden]

Would be good to also add these:

• JSON_SCHEMA_VALID()
• JSON_SCHEMA_VALIDATION_REPORT()

[dveeden]

• JSON_SCHEMA

#52779