mysql json demo.sql

use doag

drop TABLE employees;
drop table employees_old;
CREATE TABLE employees_old 
(id NUMERIC,
first_name varchar(20),
last_name varchar(20),
user_id varchar(20),
job_title varchar(20)
);

insert into employees_old values (1, 'Tamar', 'Elkayam', 'tamare', 'CFO');
insert into employees_old values (2, 'Efrat', 'Elkayam', 'efratg', 'CEO');
insert into employees_old values (3, 'Zohar', 'Elkayam', 'zohare', 'CTO');
insert into employees_old values (4, 'Ido', 'Elkayam', 'idodo', 'Team Leader');
commit;

select JSON_OBJECT('id', id, 'firstName', first_name, 'lastName', last_name) from employees_old;
select JSON_ARRAY(first_name, last_name) from employees_old;

--
drop table employees;
CREATE TABLE employees (data JSON);
INSERT INTO employees VALUES ('{"id": 1, "firstName": "Efrat"}');
INSERT INTO employees VALUES ('{"id": 2, "firstName": "Tamar"}');
INSERT INTO employees Values ('{
"id":3,
"userId":"zohare",
"jobTitleName":"CTO",
"firstName":"Zohar",
"lastName":"Elkayam",
"phoneNumber":"555-1234567",
"emailAddress":"zohar@nope.com"
}');

INSERT INTO employees Values ('{
"id":4,
"userId":"Ido",
"jobTitleName":"Team leader",
"firstName":"Ido",
"lastName":"Elkayam",
"phoneNumber":"555-1234567",
"emailAddress":{"work":"ido@nope.com", 
                "private":"nope@gmail.com"}
}');


SELECT * FROM employees;

INSERT INTO employees VALUES ('i am not a json');

--

SELECT DISTINCT JSON_EXTRACT(data, "$.firstName") as first_name FROM employees;
SELECT DISTINCT data->"$.firstName" as first_name FROM employees;

SELECT DISTINCT JSON_UNQUOTE(JSON_EXTRACT(data, "$.firstName")) as first_name FROM employees;
SELECT DISTINCT data->>"$.firstName" as first_name FROM employees;

SELECT * FROM employees
WHERE data->"$.firstName" = 'Tamar';

-- if holder does not exist, value is null
SELECT data->"$.emailAddress.work" as first_name FROM employees;

select * from 
(SELECT data, data->"$.emailAddress.work" as email FROM employees) a  
where email is not null;

select * from 
(SELECT data, data->"$.emailAddress.private" as email FROM employees) a  
where email is null;

SELECT * FROM employees WHERE data->"$.emailAddress.work" like '%nope.com';
---
SELECT JSON_SEARCH(data,'all', 'ido@nope.com') from employees;

--- Modify JSON
-- original data (no holder)
select * from employees WHERE data->"$.firstName" = 'Tamar';

-- using set
select JSON_SET(data, '$.phoneNumber', '555-1234') as JSON_OBJECT from employees WHERE data->"$.firstName" = 'Tamar';

-- using replace
select JSON_REPLACE(data, '$.phoneNumber', JSON_ARRAY('555-1234', '555-4321')) from employees WHERE data->"$.firstName" = 'Tamar';

-- Using set and replace with holder
select * from employees WHERE data->"$.firstName" = 'Zohar';

select JSON_REPLACE(data, '$.phoneNumber', JSON_ARRAY('555-1234', '555-4321')) as JSON_OBJECT from employees
WHERE data->"$.firstName" = 'Zohar';

select JSON_SET(data, '$.phoneNumber', JSON_ARRAY('555-1234', '555-4321')) as JSON_OBJECT from employees
WHERE data->"$.firstName" = 'Zohar';


--- Virtual columns and indexes

-- creating an index on json column. this does not work
create index emp_name_idx on employees(data->>"$.userId");


-- we add the generated (virtual or stored) column (preferably, virtual):
alter table employees add id numeric as (data->>"$.id");
alter table employees add id_stored numeric as (data->>"$.id") STORED;

explain select * From employees where id = 3;

create index emp_id       on employees(id);

explain select * From employees where id = 3;

-- eof