SQL JSON String Functions

CREATE TABLE documents(doc STRING CHECK(IS_VALID_JSON(doc)));
INSERT INTO documents VALUES('not valid json');

 Error 23001: violation of constraint "DOC"

INSERT INTO documents VALUES('"valid json"');
SELECT * FROM documents;

     DOC
 ------------
 "valid json"

CREATE TABLE records (old STRING, new STRING);
INSERT INTO records VALUES ('{"ID":502,"Owner":"Alice","Status":"Active"}', '{"Owner":"Alice","Status":"Inactive"}');
SELECT JSON_MERGE_PATCH(old, new) AS "Latest Record" FROM records;
                Latest records
 ----------------------------------------------
 {"ID":502,"Owner":"Alice","Status":"Inactive"}

CREATE TABLE exam_results (id INT, name STRING, answer_sheet STRING);
INSERT INTO exam_results VALUES
(12, 'John', '{"Section A":{"Question 2":{"Question":"What is the value of PI?","Answer":3.1415}}, "Section B":{"Question 7":{"Question":"What is the Capital of France?","Answer":"Paris"}}}'),
(34, 'Paul', '{"Section A":{"Question 2":{"Question":"What is the value of PI?","Answer":3.1415}}, "Section B":{"Question 7":{"Question":"What is the Capital of France?","Answer":"Paris"}}}'),
(56, 'George', '{"Section A":{"Question 2":{"Question":"What is the value of PI?","Answer":3.5}}, "Section B":{"Question 7":{"Question":"What is the Capital of France?","Answer":"Paris"}}}'),
(72, 'Ringo', '{"Section A":{"Question 2":{"Question":"What is the value of PI?","Answer":42}}, "Section B":{"Question 7":{"Question":"What is the Capital of France?","Answer":"Rome"}}}');

SELECT id, name FROM exam_results WHERE JSON_CONTAINS(answer_sheet, '3.1415', '$."Section A"."Question 2"."Answer"') = true;

 ID  NAME
 --- -----
 12  John
 34  Paul

SELECT COUNT(*) FROM exam_results WHERE JSON_CONTAINS_PATH(answer_sheet, 'ALL', '$."Section A"."Question 2"."Answer"') = true;
 [COUNT]
 --------
    4

SELECT name, JSON_EXTRACT(answer_sheet, '$."Section B"."Question 7"."Answer"') AS "Capital of France" FROM exam_results;
 NAME  Capital of France
 ------ -----------------
 John       "Paris"
 Paul       "Paris"
 George     "Paris"
 Ringo      "Rome"

SELECT name, JSON_UNQUOTE(JSON_EXTRACT(answer_sheet, '$."Section B"."Question 7"."Answer"')) AS "Capital of France" FROM exam_results;
 NAME  Capital of France
 ------ -----------------
 John        Paris
 Paul        Paris
 George      Paris
 Ringo       Rome

SELECT JSON_OBJECT() FROM dual;
 [JSON_OBJECT]
 --------------
       {}

 CREATE TABLE items (id INTEGER, object STRING);
 INSERT INTO items VALUES ('101', JSON_OBJECT(1, 55, 2, 7.8, 3, null));
 INSERT INTO items VALUES ('102', JSON_OBJECT(4, 'blue', 5, 'green'));
 INSERT INTO items VALUES ('103', JSON_OBJECT('North', 'Up', 'South', 'Down'));
 INSERT INTO items VALUES ('104', JSON_OBJECT(1, '{"number":1234}' FORMAT JSON));

 SELECT * FROM items;
  ID             OBJECT
  --- -----------------------------
  101 {"1":55,"2":7.8,"3":null}
  102 {"4":"blue","5":"green"}
  103 {"North":"Up","South":"Down"}
  104 {"1":{"number":1234}}

 CREATE TABLE employees (id INTEGER, profile STRING);
 INSERT INTO employees VALUES
 (101,'{"name":"Alice","age":34,"Department":"HR","Location":"Helsinki","Training":{"First Aid":true}}'),
 (102,'{"name":"Bob","age":30,"Department":"HR","Location":"Brussels","Training":{}}'),
 (103,'{"name":"Charlie","age":42,"Department":"Finance","Location":"Lisbon","Training":{"First Aid":true}}'),
 (104,'{"name":"Diana","Department":"Engineering","Location":"Madrid","Training":{"First Aid":false}}'),
 (105,'{"name":"Evan","age":27,"Department":"Engineering","Location":"Paris","Training":{}}'),
 (106,'{"name":"Fiona","age":29,"Department":"Engineering","Location":"Paris","Training":{"First Aid":false}}');

 SELECT id FROM employees where json_exists(profile, '$.age');
  ID
  ---
  101
  102
  103
  105
  106

 SELECT id FROM employees WHERE JSON_EXISTS(profile, 'strict $.age' UNKNOWN ON ERROR) is NULL;
  ID
  ---
  104

 SELECT id FROM employees WHERE JSON_EXISTS(profile, 'strict $.*."First Aid"' FALSE ON ERROR);
  ID
  ---
  101
  103
  104
  106

SELECT JSON_VALUE(profile, '$.age' RETURNING INTEGER) AS Age FROM employees;
  AGE
 ------
     34
     30
     42
 <null>
     27
     29

 SELECT JSON_VALUE(profile, 'lax $.Training."First Aid"' RETURNING BOOLEAN DEFAULT 'false' ON EMPTY) AS Complete FROM employees;
 COMPLETE
 ---------
   TRUE
   FALSE
   TRUE
   FALSE
   FALSE
   FALSE

SELECT JSON_VALUE(profile, 'strict $.Training."First Aid"' RETURNING BOOLEAN DEFAULT 'false' ON EMPTY) AS Complete FROM employees;
 COMPLETE
 ---------
  TRUE
  <null>
  TRUE
  FALSE
  <null>
  FALSE

SELECT JSON_QUERY(profile, '$.Training') FROM employees;
    [JSON_QUERY]
 -------------------
 {"First Aid":true}
 {}
 {"First Aid":true}
 {"First Aid":false}
 {}
 {"First Aid":false}

SELECT JSON_QUERY(profile, '$.name' WITH WRAPPER) FROM employees;
 [JSON_QUERY]
 -------------
  ["Alice"]
  ["Bob"]
  ["Charlie"]
  ["Diana"]
  ["Evan"]
  ["Fiona"]