Mf4WTQqJ

1CREATE TABLE IF NOT EXISTS `user_groups` (
2  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
3  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
4  `code` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
5  `description` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,
6  `created_at` timestamp NULL DEFAULT NULL,
7  `updated_at` timestamp NULL DEFAULT NULL,
8  PRIMARY KEY (`id`),
9  KEY `user_groups_code_index` (`code`)
10) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci;
11
12DELETE FROM `user_groups`;
13INSERT INTO `user_groups` (`id`, `name`, `code`, `description`, `created_at`, `updated_at`) VALUES
14	(1, 'Guest', 'guest', 'Default group for guest users.', '2017-11-14 03:33:04', '2017-11-14 03:33:04'),
15	(2, 'Registered', 'registered', 'Default group for registered users.', '2017-11-14 03:33:04', '2017-11-14 03:33:04'),
16	(3, 'Auditores', 'auditores', 'Son los usuarios habilitados a Autorizar las publicaciones de los usuarios.', '2017-11-17 22:18:02', '2017-11-17 22:18:15'),
17	(4, 'Estandar', 'estandar', 'Es el grupo de los usuarios estándares.', '2017-11-17 23:04:05', '2017-11-17 23:04:05');
18
19CREATE DEFINER=`root`@`localhost` PROCEDURE `demoJson`()
20	LANGUAGE SQL
21	NOT DETERMINISTIC
22	CONTAINS SQL
23	SQL SECURITY DEFINER
24	COMMENT 'AS'
25BEGIN
26  DECLARE myVariableJson VARCHAR(100);
27  DECLARE done INT DEFAULT FALSE;
28  DECLARE oneRecord VARCHAR(200);
29  DECLARE allRecord VARCHAR(600);
30  DECLARE nIndex    INT(3);
31  DECLARE cur1 CURSOR FOR SELECT JSON_OBJECT("pkey",id, "nombre",name, "codigo",code) AS campito1
32											FROM user_groups;
33  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
34
35	OPEN cur1;
36	
37	SET nIndex:=0;
38	read_loop: LOOP
39	FETCH cur1 INTO oneRecord;
40		IF done THEN
41			LEAVE read_loop;
42		END IF;
43		
44		SET nIndex:=nIndex+1;
45		IF nIndex=1 THEN
46			SET allRecord := oneRecord;
47		ELSE 
48			SELECT JSON_MERGE(allRecord, oneRecord) INTO allRecord;
49		END IF;
50
51	END LOOP;
52	
53	SET myVariableJson := JSON_OBJECT('variable1', 'value1','variable2','value2');
54	
55	SELECT JSON_MERGE(myVariableJson, allRecord) ;
56	CLOSE cur1;
57
58END
59CALL `demoJson`();