CREATE DATABASE JsonTest;
GO

USE JsonTest;

CREATE TABLE outer_table
(
   id INT NOT NULL
);

CREATE TABLE inner_table
(
   id INT NOT NULL,
   other INT NOT NULL
);

GO

INSERT INTO outer_table VALUES (1), (2);
INSERT INTO inner_table VALUES (1, 5);

GO

-- Expected that all the following would have the same output.

-- Using standard comparison
SELECT id, other,
JSON_OBJECT('id', id, 'other', other) as obj,
JSON_EXTRACT(JSON_OBJECT('id', id, 'other', other), "$.id") as id_val
FROM outer_table
LEFT JOIN inner_table USING (id)
WHERE id != 3;

-- Query fails when using WHERE to search for a valid path in the JSON
SELECT id, other,
JSON_OBJECT('id', id, 'other', other) as obj,
JSON_EXTRACT(JSON_OBJECT('id', id, 'other', other), "$.id") as id_val
FROM outer_table
LEFT JOIN inner_table USING (id)
WHERE JSON_EXTRACT(JSON_OBJECT('id', id, 'other', other), "$.id") != 3;

-- explicitly set the value to NULL
SELECT id, other,
JSON_OBJECT('id', id, 'other', other) as obj,
JSON_EXTRACT(JSON_OBJECT('id', id, 'other', other), "$.id") as id_val
FROM outer_table
LEFT JOIN inner_table USING (id)
WHERE JSON_EXTRACT(JSON_OBJECT('id', id, 'other', NULL), "$.id") != 3;

-- explicitly set the value to NULL
SELECT id, other,
JSON_OBJECT('id', id, 'other', other) as obj,
JSON_EXTRACT(JSON_OBJECT('id', id, 'other', other), "$.id") as id_val
FROM outer_table
LEFT JOIN inner_table USING (id)
HAVING JSON_EXTRACT(JSON_OBJECT('id', id, 'other', other), "$.id") != 3;