在MySQL中,可以使用JSON_EXTRACT函數(shù)和JSON_UNQUOTE函數(shù)對JSON字符串進(jìn)行嵌套查詢。
例如,假設(shè)有一個(gè)包含JSON數(shù)據(jù)的表products:
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(50),
details JSON
);
其中details字段存儲了產(chǎn)品的詳細(xì)信息,例如:
INSERT INTO products (id, name, details) VALUES (1, 'Product1', '{"price": 100, "category": "Electronics", "specs": {"weight": 1, "dimensions": {"width": 10, "height": 5, "depth": 3}}}');
現(xiàn)在,我們可以使用JSON_EXTRACT函數(shù)和JSON_UNQUOTE函數(shù)進(jìn)行嵌套查詢:
SELECT
id,
name,
JSON_UNQUOTE(JSON_EXTRACT(details, '$.category')) AS category,
JSON_UNQUOTE(JSON_EXTRACT(details, '$.specs.weight')) AS weight,
JSON_UNQUOTE(JSON_EXTRACT(details, '$.specs.dimensions.width')) AS width
FROM products;
這將返回如下結(jié)果:
| id | name | category | weight | width |
|----|----------|-------------|--------|-------|
| 1 | Product1 | Electronics | 1 | 10 |
這樣就可以通過嵌套查詢來提取JSON字符串中的嵌套數(shù)據(jù)。