SQLize Online / PHPize Online  /  SQLtest Online

create table tbl (id int, name varchar(50), val json); insert into tbl values (1, 'tipo_progetto', '"test"'), (2, 'budget', '100'), (3, 'lista_fornitori', '{"nome": "Siemens", "test": false}'), (4, 'allegati1', '[{"id": 1, "test": "Hello"}, {"id": 5, "test": "World"}]'), (5, 'allegati2', '[{"id": 2, "test": "Hello"}, {"id": 7, "test": "World"}]'), (6, 'allegati3', '[{"id": 3, "test": "Hello"}, {"id": 9, "test": "World"}]'), (7, 'societa/funzione', '"HR"'), (8, 'societa/funzione', '"PU"'), (9, 'test_boolean', 'true'), (10, 'data_inserimento1', '"2024-12-31 12:00:00"'), (11, 'data_inserimento2', '"2025-01-02 16:00:00"'), (12, 'data_inserimento3', '"2025-02-10 12:00:00"'), (13, 'data_inserimento4', '"2025-01-01 00:00:00"'); select * from tbl where json_contains(val, '"Siemens"', '$.nome'); select * from tbl where val->"$[*].test" != "World"; select * from tbl where val->"$" = "HR"; select JSON_TYPE(val) from tbl; select *, str_to_date(val->>"$", "%Y-%m-%d %T") as extracted_date from tbl where str_to_date(val->>"$", "%Y-%m-%d %T") IS NOT NULL;