Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment