Month: March 2018

— In MariaDB or MySQL, here is a demonstration of how to easily write 
— JSON to a table, and providing an easy way for normal SQL to reference 
— that JSON. Storing JSON can be a definition of a NoSQL database
— Note, this is intended for you to copy/paste into your SQL tool, and play. 
— It is self explanatory!


CREATE DATABASE tst;
USE tst;


— DROP TABLE tab;
CREATE TABLE tab (row_id INTEGER AUTO_INCREMENT PRIMARY KEY,
  document_store JSON);


— Insert two JSON objects into the column ‘document_store’ of type JSON.
INSERT INTO tab (document_store) VALUES (‘{“fname”:”jim”,
     “lname”:”jackson”,
     “stuff”:”nothing here”}’);
INSERT INTO tab (document_store) VALUES (‘{“fname”:”troy”,
     “lname”:”Frericks”,
     “stuff”:”A bunch of stuff”}’);
SELECT * FROM tab;


— select data from the JSON typed column
SELECT JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.fname’)),
 JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.lname’)),
 JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.stuff’))
FROM tab
WHERE JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.fname’)) = ‘troy’;


— create virtual/stored columns that ‘expose’ the data in the JSON object
ALTER TABLE tab ADD json_fname VARCHAR(32) AS
 (JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.fname’))) VIRTUAL;


ALTER TABLE tab ADD json_lname VARCHAR(32) GENERATED ALWAYS AS
 (JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.lname’))) VIRTUAL;


ALTER TABLE tab ADD json_stuff VARCHAR(32) GENERATED ALWAYS AS
 (JSON_UNQUOTE(JSON_EXTRACT(document_store,’$.stuff’))) STORED;


SELECT * FROM tab;


— prove that virtual or stored columns can be indexed
CREATE INDEX idx_tab_fname ON tab (json_fname);
CREATE INDEX idx_tab_sname ON tab (json_lname);
CREATE INDEX idx_tab_stuff ON tab (json_stuff);






Troy Frericks.
blog 23-Mar-2018
=
Copyright 2015-2018 by Troy Frericks, http://dba.frericks.us/.
#