Extracting Values from JSON Type Fields with Extra Quotes on Both Sides

Translated
1

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

Original topic: json类型字段提取值，两边多出引号

| username: 大飞飞呀

[Test Environment] TiDB
[TiDB Version] 5.7.25-TiDB-v5.0.4
[Reproduction Path] Operations performed to encounter the issue
CREATE TABLE test.city (
id INT PRIMARY KEY,
detail JSON
);
insert into test.city (id, detail) values(1, ‘{“name”:“Beijing”}’);
insert into test.city (id, detail) values(3, ‘[{“name”:“Beijing”},{“name”:“Shanxi”}]’);
[Encountered Issue: Phenomenon and Impact]
SELECT JSON_EXTRACT(detail, ‘$[1].name’) from test.city
JSON extraction includes quotes on both sides
image

[Resource Configuration]
[Attachments: Screenshots/Logs/Monitoring]

2
| username: buddyyuan | Original post link

This is normal, MySQL is the same. To display without “”, there are two methods:

  1. Method 1
select detail->>'$[1].name' from test.city;
  1. Method 2, use a JSON_UNQUOTE function.
select JSON_UNQUOTE(JSON_EXTRACT(detail,'$[1].name')) from test.city;
3
| username: tidb狂热爱好者 | Original post link

This requires an upgrade to version 6.3 to support JSON.

4
| username: system | Original post link

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