SQLize | PHPize | SQLtest

CREATE TABLE `SyncopeUser` ( `id` varchar(36) NOT NULL, `creationContext` varchar(255) DEFAULT NULL, `creationDate` datetime(3) DEFAULT NULL, `creator` varchar(255) DEFAULT NULL, `lastChangeContext` varchar(255) DEFAULT NULL, `lastChangeDate` datetime(3) DEFAULT NULL, `lastModifier` varchar(255) DEFAULT NULL, `status` varchar(255) DEFAULT NULL, `changePwdDate` datetime(3) DEFAULT NULL, `cipherAlgorithm` varchar(20) DEFAULT NULL, `failedLogins` int(11) DEFAULT NULL, `lastLoginDate` datetime(3) DEFAULT NULL, `mustChangePassword` int(11) DEFAULT NULL, `password` varchar(255) DEFAULT NULL, `passwordHistory` text DEFAULT NULL, `securityAnswer` varchar(255) DEFAULT NULL, `suspended` int(11) DEFAULT NULL, `token` text DEFAULT NULL, `tokenExpireTime` datetime(3) DEFAULT NULL, `username` varchar(255) DEFAULT NULL, `plainAttrs` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`plainAttrs`)), `REALM_ID` varchar(36) DEFAULT NULL, `SECURITYQUESTION_ID` varchar(36) DEFAULT NULL, PRIMARY KEY (`id`)); CREATE OR REPLACE ALGORITHM = UNDEFINED VIEW `user_search_attr` AS select `u`.`id` AS `any_id`, `attrs`.`schema_id` AS `schema_id`, `attrs`.`booleanvalue` AS `booleanvalue`, `attrs`.`datevalue` AS `datevalue`, `attrs`.`doublevalue` AS `doublevalue`, `attrs`.`longvalue` AS `longvalue`, `attrs`.`stringvalue` AS `stringvalue` from (`SyncopeUser` `u` left join JSON_TABLE(coalesce(`u`.`plainAttrs`, '[]'), '$[*]' COLUMNS (`schema_id` varchar(255) PATH '$.schema', NESTED PATH '$.values[*]' COLUMNS (`booleanvalue` int(11) PATH '$.booleanValue', `datevalue` varchar(32) PATH '$.dateValue', `doublevalue` double PATH '$.doubleValue', `longvalue` bigint(20) PATH '$.longValue', `stringvalue` varchar(255) PATH '$.stringValue'))) `attrs` on (1 = 1)) where `attrs`.`schema_id` is not null and (`attrs`.`booleanvalue` is not null or `attrs`.`datevalue` is not null or `attrs`.`doublevalue` is not null or `attrs`.`longvalue` is not null or `attrs`.`stringvalue` is not null); analyze format=json select * from user_search_attr where schema_id = 'familyName' and stringvalue = 'Bergmann';