去除JSON键中的前导和尾随空格

Question

我正在尝试使用JSON_EXTRACT从我的MariaDB服务器获取JSON值。然而，有些JSON键有很多空格，如行末、空格、制表符等。数据已经存在。因此，我无法给出正确的键名，因为该键包含空格。请注意，JSON值中也存在空格，但我们可以使用TRIM()函数从值中删除空格。但是我们该如何修剪键名呢？

例如：

CREATE TABLE test.product_json_table (
   id INT AUTO_INCREMENT NOT NULL,
   product VARCHAR(20) NOT NULL,
   description LONGTEXT ASCII,
  PRIMARY KEY (id),
    CHECK (JSON_VALID(description))
) ENGINE = InnoDB ROW_FORMAT = DEFAULT;



INSERT INTO test.product_json_table(product, description) 
VALUES( 'truck_space', '{"     \r\nwheels  ": 4, "seats": 3, "  fuel   ": "diesel", "  \r\n mileage     ": 8}');

下面的查询无法工作：

SELECT id, product, description 
FROM test.product_json_table
WHERE JSON_EXTRACT(description, '$.wheels') > 2;

查询不起作用是因为JSON键“wheels”之间存在空格。同样的情况也出现在“mileage”键中。

我们该如何解决这个问题呢？提前感谢您的帮助。

- SRaj

---

我会通过在将输入插入数据库之前修剪空格来解决问题。但是您也可以提取带引号的键：JSON_EXTRACT(description, '$." \r\nwheels "')。我现在不在MySQL 5.7实例附近，所以无法测试。 - Bill Karwin

此外，这与您的问题无关，但我建议使用JSON 数据类型而不是 LONGTEXT。 - Bill Karwin

我只能说垃圾进垃圾出。 - Rick James

感谢您的建议。我忘了提到的问题是我没有数据库的写入权限。它由另一个应用程序填充。所以让我尝试一下Bill的建议。哦，我正在使用MariaDB 10.2.3，因此没有JSON数据类型，他们建议使用LONGTEXT。尝试后会更新。谢谢大家！ - SRaj

Rick，你说得对。这是一个典型的GIGO问题。但是垃圾数据是由另一个应用程序倒入数据库中的，我们无法控制太多。 - SRaj

嗨，比尔，尼克下面建议的方法有效。感谢你的帮助。 - SRaj

Answer 1

除了@BillKarwin的建议，在将文本输入数据库之前修剪空格，您还可以更新数据库中的所有值以删除不必要的空格：

UPDATE product_json_table
SET description = REGEXP_REPLACE(description, '\\s|\\r|\\n','');

那么您最初的查询将有效：

SELECT id, product, description 
FROM product_json_table
WHERE JSON_EXTRACT(description, '$.wheels') > 2;

输出：

id  product         description
1   truck_space     {"wheels":4,"seats":3,"fuel":"diesel","mileage":8}

在 dbfiddle 上演示

更新

你也可以即时使用空格替换，虽然这比使用上面的 UPDATE 查询永久性地删除更低效：

SELECT id, product, REGEXP_REPLACE(description, '\\s|\\r|\\n','') AS description 
FROM product_json_table
WHERE JSON_EXTRACT(REGEXP_REPLACE(description, '\\s|\\r|\\n',''), '$.wheels') > 2

输出：

id  product         description
1   truck_space     {"wheels":4,"seats":3,"fuel":"diesel","mileage":8}

在 dbfiddle 上的演示

- Nick

---

似乎只有这个可以工作。没有更新，这是无法工作的。正在尝试获取对数据库的写入访问权限，不确定我们是否能够成功。 - SRaj

哇！谢谢Nick。你救了我的一天！标记为已回答。 - SRaj

Answer 2

你可以使用

REGEXP_REPLACE(query, '\\s|\\r|\\n','')

看见

CREATE TABLE product_json_table (
   id INT AUTO_INCREMENT NOT NULL,
   product VARCHAR(20) NOT NULL,
   description LONGTEXT ASCII,
  PRIMARY KEY (id),
    CHECK (JSON_VALID(description))
) 

✓

INSERT INTO product_json_table(product, description) 
VALUES( 'truck_space', REGEXP_REPLACE('{"     \r\nwheels  ": 4, "seats": 3, "  fuel   ": "diesel", "  \r\n mileage     ": 8}', '\\s|\\r|\\n',''));

✓

select * from product_json_table

id | product     | description                                       
-: | :---------- | :-------------------------------------------------
 1 | truck_space | {"wheels":4,"seats":3,"fuel":"diesel","mileage":8}

db<>fiddle here

db<>fiddle 这里

- Derviş Kayımbaşıoğlu