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;

Please add the query you used to list the elements.

Nimeshka Srimal
– Nimeshka Srimal

2019-11-26 06:58:00 +00:00
Commented Nov 26, 2019 at 6:58 — Nimeshka Srimal
– Nimeshka Srimal, Commented Nov 26, 2019 at 6:58
@Nimeshka Srimal added

soumya_acube
– soumya_acube

2019-11-26 07:14:35 +00:00
Commented Nov 26, 2019 at 7:14 — soumya_acube
– soumya_acube, Commented Nov 26, 2019 at 7:14

soumya_acube · Accepted Answer · 2019-11-26 09:48:47Z

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;

answered Nov 26, 2019 at 9:48

soumya_acube

436 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

humbleiam Over a year ago

That was really helpful.

Collectives™ on Stack Overflow

Mysql JSON Nested Array using JSON DATA TYPE

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related