Mysql JSON Nested Array using JSON DATA TYPE

Question

I am trying to list elements in a nested array using JSON data type, but am not able to do that

This is my 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 } ]}'

);

This is what i tried

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

Answer 1

I used JSON_TABLE for listing and it worked,

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;