MySQL - 查询 JSON 列时转义双引号和括号

Question

在查询包含对象数组的 JSON 列时，如何转义双引号和括号？

当我运行它时，它起作用了......

SELECT boarded, boarded->>"$[0].division.id" AS divisionId FROM onboarder

但这并不...

SELECT boarded, boarded->>"$[*].division.id" AS divisionId FROM onboarder

我认为双箭头逃脱了一切，只买回了值(value)。这就是我所拥有的...

Answer 1

->> 运算符不会转义任何内容。它只是将结果转换为标量数据类型，例如 text 或 integer。这相当于JSON_UNQUOTE(JSON_EXTRACT(...)) :

Unquotes JSON value and returns the result as a utf8mb4 string.

我们可以通过使用结果集创建新表并检查它创建的数据类型来演示 -> 和 ->> 之间的区别。

create table t as SELECT boarded, boarded->"$[*].division.id" AS divisionId FROM onboarder;

show create table t;
CREATE TABLE `t` (
  `boarded` json DEFAULT NULL,
  `divisionId` json DEFAULT NULL
);

select * from t;
+------------------------------------+--------------+
| boarded                            | divisionId   |
+------------------------------------+--------------+
| [{"division": {"id": "8ac7a..."}}] | ["8ac7a..."] |
+------------------------------------+--------------+

注意divisionId是一个json文档，它是一个数组。

如果我们使用->>，就会发生这样的情况:

create table t as SELECT boarded, boarded->>"$[*].division.id" AS divisionId FROM onboarder;

show create table t;

CREATE TABLE `t` (
  `boarded` json DEFAULT NULL,
  `divisionId` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
)

select * from t;
+------------------------------------+--------------+
| boarded                            | divisionId   |
+------------------------------------+--------------+
| [{"division": {"id": "8ac7a..."}}] | ["8ac7a..."] |
+------------------------------------+--------------+

没有明显的区别，因为方括号仍然存在。但后者存储为 longtext 数据类型。

<小时/>

回复您的评论:

How can I return divisionId as a value so that it's not an array and quoted?

您在查询中使用了$[*]，此模式的含义是将数组的所有元素作为数组返回。要获取单个值，您需要查询数组的单个元素，如第一个示例所示:

boarded->>'$[0].division.id'

如果您不使用 JSON，而是将数据存储在传统表中，其中一个分区位于其自己的行中，并且该分区的每个字段位于其自己的列中，那么这会容易得多。

CREATE TABLE divisions (
  division_id VARCHAR(...) PRIMARY KEY,
  merchant ...
);

CREATE TABLE merchants (
  merchant_id ... PRIMARY KEY,
  division_id VARCHAR(...),
  FOREIGN KEY (division_id) REFERENCES division(division_id)
);

我看到的关于开发人员如何尝试在 MySQL 中使用 JSON 的示例越多，我就越确信这是一个坏主意。