= SQL
== SQL:2016
=== JSON

via paper "The New and Improved SQL:2016 Standard" by Michels, et al.

CREATE TABLE T ( Id INTEGER PRIMARY KEY, Jcol CHARACTER VARYING (5000) CHECK (Jcol IS JSON) );
SELECT * FROM T WHERE Jcol IS JSON;
SELECT Id FROM T WHERE JSON_EXISTS( Jcol, 'strict $.address' );
-- strict != lax: lax considers one JSON value equivalent to a list with one value
SELECT Id, JSON_VALUE( Jcol, 'lax $.phoneNumber[0].number' ) AS Firstphone FROM T;
SELECT Id, JSON_VALUE( Jcol, 'lax $.phoneNumber ? ( @.type == "fax" ).number' ) AS Fax FROM T;
-- JSON_QUERY extracts not only scalars
SELECT Id, JSON_QUERY( Jcol, 'lax $.address' ) AS Address FROM T;
SELECT T.Id, Jt.Name, Jt.Zip FROM T, JSON_TABLE( T.Jcol, 'lax $' COLUMNS(
	Name VARCHAR(30) PATH 'lax $.Name'
	Zip VARCHAR(5) PATH 'lax $.address.postalCode' )) AS Jt
-- NESTED PATH can also be used to deflatten, moves the context variable $
SELECT T.Id, Jt.Name, Jt.Type, Jt.Number FROM T, JSON_TABLE( T.Jol, 'lax $' COLUMNS(
	Name VARCHAR(30) PATH 'lax $.Name'
	NESTED PATH 'lax $.phoneNumber[*]'
	COLUMNS (
		Type VARCHAR(10) PATH 'lax $.type'
		Number VARCHAR(12) PATH 'lax $.number' ))) AS Jt
… WHERE JSON_EXISTS( T.Jcol, 'lax $.phoneNumber' )
'strict $.* ? ( @.type == "array" && @.size() > 1 )'