By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE my_test(id INT,data TEXT);
INSERT INTO my_test values
(1,"{'field1': '123432', 'field2':'TEST', 'field3': ''}"),
(2,"{'field1': '322345', 'field2':'TEST 2', 'field3': 'TEST'}");
SELECT * FROM my_test;
| id | data |
|---|---|
| 1 | {'field1': '123432', 'field2':'TEST', 'field3': ''} |
| 2 | {'field1': '322345', 'field2':'TEST 2', 'field3': 'TEST'} |
SELECT *,
JSON_VALID(data) AS check_if_json_data_is_valid
FROM my_test;
| id | data | check_if_json_data_is_valid |
|---|---|---|
| 1 | {'field1': '123432', 'field2':'TEST', 'field3': ''} | 0 |
| 2 | {'field1': '322345', 'field2':'TEST 2', 'field3': 'TEST'} | 0 |
SELECT *,
REPLACE(data,'\'','"') AS replaced_quotes,
JSON_VALID(REPLACE(data,'\'','"')) AS check_if_json_data_is_valid
FROM my_test;
| id | data | replaced_quotes | check_if_json_data_is_valid |
|---|---|---|---|
| 1 | {'field1': '123432', 'field2':'TEST', 'field3': ''} | {"field1": "123432", "field2":"TEST", "field3": ""} | 1 |
| 2 | {'field1': '322345', 'field2':'TEST 2', 'field3': 'TEST'} | {"field1": "322345", "field2":"TEST 2", "field3": "TEST"} | 1 |
SELECT *,
JSON_UNQUOTE(JSON_EXTRACT(REPLACE(data,'\'','"'), '$.field3')) AS field3
FROM my_test
HAVING field3 != ''
| id | data | field3 |
|---|---|---|
| 2 | {'field1': '322345', 'field2':'TEST 2', 'field3': 'TEST'} | TEST |