mysql - 使用JSON DATA TYPE的Mysql JSON嵌套数组

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

这是我的桌子。

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;

最佳答案

我使用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;

关于mysql - 使用JSON DATA TYPE的Mysql JSON嵌套数组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/59045275/