use mytools;

DROP TABLE IF EXISTS customer_json;

-- a table with a JSON attribute:
CREATE TABLE customer_json ( 
  id int auto_increment primary key, 
  customer json
);

-- insert JSON-based data into the table:
INSERT INTO customer_json(customer) 
VALUES (
   '{ "cust_id": "1000", "first_name": "John", "last_name": "Smith", "address": "123 Main Street", "city": "Fremont", "state": "CA", "zip_code": "94123"}'
),
(
   '{ "cust_id": "2000", "first_name": "Jane", "last_name": "Jones", "address": "456 Front Street", "city": "Fremont", "state": "CA", "zip_code": "95015"}'
);

-- display the values of the first_name and last_name attributes:
SELECT id, customer->'$.first_name', customer->'$.last_name'
FROM customer_json;

-- the JSON_ARRAY() function creates arrays:
SELECT JSON_ARRAY(1000, "Deep", "Dish", "Pizza");

-- the JSON_OBJECT() function creates objects:
SELECT JSON_OBJECT(1000, "Deep", "Dish", "Pizza");

-- the JSON_QUOTE() function quotes a string as a JSON value:
SELECT JSON_QUOTE('[1000, "Deep", "Dish", "Pizza"]');

