JSON Data Type in Relational Databases

Relational Database History

Database Royalty for 40 Years

Since 1970's

Relational Algebra and Set Theory

Storage... Memory... Data Dictionary... Query Language

Benefits

Addressed limits of: (1) Flat Files (2) Hierarchical Data and (3) Network Data

Duplicate Data

Inefficient Searching

Difficulty Maintaining Program Code For Data Access

NoSQL

A New Breed of Database

Data Category	Description
Key Value	Simple KV
Columnar	Good for Analytical Processing
Graph	Relationships b/w Entities
Document	Embedding Joins

Version	Feature(s)
9.2	JSON Validation
9.3	Extract Operators
9.4	JSONB
9.5	JSONB Modifiers
9.6	jsonb_insert()
10	Full Text Search for JSON/B

JSON	JSONB (Binary)
Preserves whitespace	Removes whitespace b/w keys
Preserves key order	Arbitrary key order
Duplicate keys	No duplicates
Heavy writes (No indexes)	Heavy reads (Indexes allowed)
E.g. Log files; API responses	Recommended for general use

JSON (Binary)
Removes whitespace b/w keys
Arbitrary key order
No duplicates
Indexes allowed via virtual columns
1 Gb per field

LONGTEXT aka JSON
Preserves whitespace
Preserves key order
Duplicate keys
Indexes allowed via virtual columns
4 Gb per field
CHECK Constraint required to validate document on column definition

TEXT
Preserves whitespace
Preserves key order
Duplicate keys
No enforced field limit
json() function call required to validate document on statement

Adding a JSON compatible column

PostgreSQL

ALTER TABLE users ADD contacts JSONB;

MySQL

ALTER TABLE users ADD contacts JSON;

MariaDB

ALTER TABLE users ADD contacts JSON CHECK(JSON_VALID(user));

SQLite

ALTER TABLE users ADD contacts TEXT;

PostgreSQL & MySQL:
Automatic validation is performed on JSON documents when stored in JSON columns. Invalid documents produce an error.

PostgreSQL:
has an optimized storage format. JSON documents stored in JSONB format.
MySQL:
has an optimized storage format. JSON documents stored in JSON columns are converted to an internal format that permits quick read access to document elements. When the server later must read a JSON value stored in this binary format, the value need not be parsed from a text representation. The binary format is structured to enable the server to look up subobjects or nested values directly by key or array index without reading all values before or after them in the document.

Insert

PostgreSQL/MySQL/MariaDB

INSERT INTO users (user_name, contacts) VALUES ('kenny', '{ "email": "kenny@aol.com", "twitter": "@drken"}');

INSERT INTO users (user_name, contacts) VALUES ('sam', '{ "email": "sam@yahoo.com", "twitter": "@sam88", "facebook": "sam88"}');

SQLite

INSERT INTO users (user_name, contacts) VALUES ('kenny', json('{ "email": "kenny@aol.com", "twitter": "@drken"}'));

PostgreSQL

UPDATE users SET contacts = JSONB_INSERT(contacts, '{phone}', '"314-222-2222"') WHERE user_name = 'kenny';

Select

(Including returning a subsection of the document)

PostgreSQL

SELECT user_name, contacts -> 'twitter' AS twitter FROM users WHERE user_name = 'kenny';

SELECT user_name, contacts ->> 'twitter' AS twitter FROM users WHERE user_name = 'kenny';

SELECT user_name, contacts ->> 'twitter' AS twitter FROM users WHERE contacts ->> 'twitter' IS NOT NULL;

MySQL

SELECT user_name, contacts->'$.twitter' AS twitter FROM users WHERE user_name = 'kenny';

SELECT user_name, contacts->>'$.twitter' AS twitter FROM users WHERE user_name = 'kenny';

SELECT user_name, contacts->>'$.twitter' AS twitter FROM users WHERE contacts->>'$.twitter' IS NOT NULL;

MariaDB/SQLite

SELECT user_name, JSON_VALUE(contacts, '$.twitter') AS twitter FROM users WHERE user_name = 'kenny';

Update

(Including updating a subsection of the document)

PostgreSQL

UPDATE users SET contacts = JSONB_SET(contacts, '{twitter}', '"@fred"') WHERE user_name = 'kenny';

MySQL/MariaDB/SQLite

UPDATE users SET contacts = JSON_SET(contacts, '$.twitter', '@fred') WHERE user_name = 'kenny';

Delete

(Including deleting a subsection of the document)

PostgreSQL

UPDATE users SET contacts = contacts - 'twitter' WHERE user_name = 'kenny';

MySQL/MariaDB/SQLite

UPDATE users SET contacts = JSON_REMOVE(contacts, '$.twitter') WHERE user_name = 'kenny';

Virtual Index on a JSON attribute

PostgreSQL

CREATE INDEX idx_users_contacts ON users USING GIN (contacts);
CREATE INDEX idx_users_contacts_email ON users USING GIN ((contacts -> 'email'));

MySQL

/* Create the virtual column: */
ALTER TABLE users ADD contacts_email VARCHAR(255) AS (contacts->>'$.email');
ALTER TABLE users ADD contacts_email VARCHAR(255) AS (contacts->>'$.email') STORED;

/* Create your traditional index: */
ALTER TABLE users ADD INDEX (contacts_email);
ALTER TABLE users ADD INDEX (contacts_email_stored);

MariaDB

/* Create the virtual column: */
ALTER TABLE users ADD COLUMN contacts_email VARCHAR(255) AS (JSON_VALUE(contacts, '$.email')) VIRTUAL;

/* Create your traditional index: */
ALTER TABLE users ADD INDEX (contacts_email);
ALTER TABLE users ADD INDEX (contacts_email_stored);

JSON Data Type in Relational Databases

Storing & retrieving JSON data in a RDBMS