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, 'data_inserimento', '"2024-10-10 12:33:45"'), (10, 'test_boolean', 'true'); 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 extracted_date IS NOT NULL;