使用 JSON 数据类型的 Mysql JSON 嵌套数组

Question

我正在尝试使用 JSON 数据类型列出嵌套数组中的元素，但无法做到这一点

这是我的 table 。

INSERT INTO events(event_name, visitor ,properties, browser) 
VALUES (
  'pageview', 
   '1',
   '{ "page": "/" }',
   '{ "name": "UC Browser", "os": "Mac", "resolution": { "x": 1920, "y": 1080 } }'

),
('pageview', 
   '2',
   '{ "page": "/" }',
   '{ "name": "Safari", "os": "Mac", "resolution": [{ "x": 1920, "y": 1080 },{ "x": 1720, "y": 1080 } ]}'

);

这是我尝试过的

SELECT id, browser->>'$.name' name, browser->>'$.resolution.x' x_resolution, browser->>'$.resolution.y' y_resolution
FROM events where id=2;

Answer 1

我使用 JSON_TABLE 进行列表并且它有效，

DROP TABLE IF EXISTS events;

CREATE TABLE events( 
  id int auto_increment primary key, 
  event_name varchar(255), 
  visitor varchar(255), 
  properties json, 
  browser json
);
INSERT INTO events(event_name, visitor,properties, browser) 
VALUES 
 (

 'pageview', 
   '1',
   '{ "page": "/" }',
   '{ "name": "UC", "os": "Mac", "resolution": [{ "x": 10, "y": 100 }]}'

),
('pageview', 
   '2',
   '{ "page": "/" }',
   '{ "name": "Safari", "os": "Mac", "resolution": [{ "x": 1920, "y": 1080 },{ "x": 1960, "y": 1080 } ]}'

);
DROP TABLE  IF EXISTS browsers;

CREATE TABLE browsers( 
  id int auto_increment primary key, 
  browser varchar(255),
  x_resolution varchar(255),
  y_resolution varchar(255)
);

SELECT resolution.*,browser->> '$.name' Name,browser->> '$.os' OS
FROM events, 
     JSON_TABLE(browser, '$.resolution[*]' COLUMNS (
                x_val VARCHAR(40)  PATH '$.x',
                y_val VARCHAR(100) PATH '$.y')
) resolution;