面试题：MySQL中JSON数据类型的复杂查询与索引

Question

假设有一张表`products`，其中有一个JSON类型的列`details`，`details`存储了产品的详细信息，结构如下：`{"brand":"某品牌","features":["feature1","feature2","feature3"],"price":50}`。请写出SQL语句，查询出所有品牌为'某品牌'且价格大于30的产品，同时为提高该查询效率，如何为`details`列建立合适的索引？

Answer 1

查询语句

假设使用PostgreSQL数据库，因为其对JSON类型支持较好，查询语句如下：

SELECT * 
FROM products 
WHERE details ->> 'brand' = '某品牌' 
  AND (details ->> 'price')::numeric > 30;

索引建立

在PostgreSQL中，可以使用GIN索引来提高JSON类型列的查询效率。建立索引语句如下：

CREATE INDEX idx_products_details ON products USING gin ((details jsonb_path_ops));

这里假设数据库为PostgreSQL，不同数据库对于JSON类型操作和索引建立方式会有差异，例如MySQL从8.0版本开始支持JSON数据类型，其建立索引方式为：

-- MySQL 8.0+
CREATE INDEX idx_products_details ON products ((JSON_UNQUOTE(JSON_EXTRACT(details, '$.brand'))));

查询语句为：

SELECT * 
FROM products 
WHERE JSON_UNQUOTE(JSON_EXTRACT(details, '$.brand')) = '某品牌' 
  AND JSON_UNQUOTE(JSON_EXTRACT(details, '$.price')) > 30;