在mysql 5.7中从json字段创建视图？

Question

我有一个带有json对象的字段，其结构如下：

{
"12edd9df-7aa3-5916-8e06-4f21dfcb5920": {"text": "value1", "deleted": true},
.... 
"23767bc4-2e30-5fca-8683-a04c06604e96": {"text": "value2", "deleted": true}
}

创建具有以下结构的视图的sql将是什么：

uuid                                 | text    | deleted
12edd9df-7aa3-5916-8e06-4f21dfcb5920 | value1  | true
...

约束条件是我使用MySQL5.7，不能创建过程。

Answer 1

这里有个解决办法。您必须创建一个数字表才能工作。

CREATE TABLE n ( n INT PRIMARY KEY );
INSERT INTO n (n) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);

SELECT uuid, 
  JSON_EXTRACT(data, CONCAT('$.', uuid, '.text')) AS text, 
  JSON_EXTRACT(data, CONCAT('$.', uuid, '.deleted')) AS deleted
FROM (
  SELECT data, JSON_EXTRACT(JSON_KEYS(data), CONCAT('$[', n, ']')) AS uuid
  FROM mytable CROSS JOIN n
) AS t 
WHERE t.uuid IS NOT NULL;

您应该重新考虑对这些数据使用JSON。如果使用普通列，那么编写、调试和维护代码就容易多了。

你应该能够做到这一点：

SELECT uuid, text, deleted FROM mytable;