MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE `activity` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `createdAt` datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  `deletedAt` datetime(6) DEFAULT NULL,
  `private` tinyint NOT NULL DEFAULT '0',
  `topic` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `description` text COLLATE utf8mb4_unicode_ci,
  `authorId` int NOT NULL,
  `startDate` datetime NOT NULL,
  `endDate` datetime DEFAULT NULL,
  `location` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `competencies` json DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_0e60c1982a77d3e092ef4f3bef9` (`authorId`) /* ,
  CONSTRAINT `FK_0e60c1982a77d3e092ef4f3bef9` FOREIGN KEY (`authorId`) REFERENCES `users` (`id`) ON DELETE CASCADE 
                                                    */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO `activity` VALUES 
(1,'testActivity123','2021-06-21 05:14:44.644150',NULL,0,NULL,NULL,18,'2021-06-21 05:14:45',NULL,NULL,'{\"Test activityCompetence for createCompetence\": 1}'),
(2,'testActivity123','2021-06-21 05:14:44.644150',NULL,0,NULL,NULL,18,'2021-06-21 05:14:45',NULL,NULL,'{\"Test\": 3}'),
(3,'testActivity123','2021-06-21 05:14:44.644000',NULL,0,NULL,NULL,18,'2021-06-21 05:14:45',NULL,NULL,'{\"Test activityCompetence for createCompetence\": 1}'),
(4,'test','2021-06-21 05:30:29.686046',NULL,0,NULL,NULL,51,'2021-06-21 05:30:30',NULL,NULL,'{\"competenceForSearch\": 20, \"blabla text\": 2}'),
(5,'test','2021-06-21 05:30:29.686046',NULL,0,NULL,NULL,51,'2021-06-21 05:30:30',NULL,NULL,'{\"firstCompetence\": 2, \"fourthCompetence\": 4}'),
(6,'test','2021-06-21 05:30:29.686046',NULL,0,NULL,NULL,51,'2021-06-21 05:30:30',NULL,NULL,'{\"firstCompetence\": 2, \"secondCompetence\": 2}'),
(7,'test','2021-06-21 05:30:29.686046',NULL,0,NULL,NULL,51,'2021-06-21 05:30:30',NULL,NULL,'{\"one\": 1, \"two\": 2, \"three\": 3}'),
(8,'test','2021-06-21 05:30:29.686046',NULL,0,NULL,NULL,51,'2021-06-21 05:30:30',NULL,NULL,'{\"one\": 1, \"two\": 2, \"three\": 3, \"four\": 4}'),
(9,'test','2021-06-21 05:30:29.686046',NULL,0,NULL,NULL,51,'2021-06-21 05:30:30',NULL,NULL,'{\"one\": 1, \"four\": 4, \"three\": 3}');

SELECT id, CAST(competencies AS CHAR) jsontext FROM activity;


id
jsontext


1
{"Test activityCompetence for createCompetence": 1}

2
{"Test": 3}

3
{"Test activityCompetence for createCompetence": 1}

4
{"blabla text": 2, "competenceForSearch": 20}

5
{"firstCompetence": 2, "fourthCompetence": 4}

6
{"firstCompetence": 2, "secondCompetence": 2}

7
{"one": 1, "two": 2, "three": 3}

8
{"one": 1, "two": 2, "four": 4, "three": 3}

9
{"one": 1, "four": 4, "three": 3}


SET @criteria := '["Test"]';

SELECT activity.id
FROM activity
CROSS JOIN JSON_TABLE(@criteria,
                      '$[*]' COLUMNS (keyname VARCHAR(255) PATH '$')) jsontable
GROUP BY activity.id
HAVING !SUM(JSON_EXTRACT(activity.competencies, CONCAT('$.', jsontable.keyname)) IS NULL)


id


2


SET @criteria := '["one", "two", "three"]';

SELECT activity.id
FROM activity
CROSS JOIN JSON_TABLE(@criteria,
                      '$[*]' COLUMNS (keyname VARCHAR(255) PATH '$')) jsontable
GROUP BY activity.id
HAVING !SUM(JSON_EXTRACT(activity.competencies, CONCAT('$.', jsontable.keyname)) IS NULL)


id


7

8


SET @criteria := '["one", "three"]';

SELECT activity.id
FROM activity
CROSS JOIN JSON_TABLE(@criteria,
                      '$[*]' COLUMNS (keyname VARCHAR(255) PATH '$')) jsontable
GROUP BY activity.id
HAVING !SUM(JSON_EXTRACT(activity.competencies, CONCAT('$.', jsontable.keyname)) IS NULL)


id


7

8

9


SET @criteria := '["test"]';

SELECT activity.id
FROM activity
CROSS JOIN JSON_TABLE(@criteria,
                      '$[*]' COLUMNS (keyname VARCHAR(255) PATH '$')) jsontable
GROUP BY activity.id
HAVING !SUM(JSON_EXTRACT(activity.competencies, CONCAT('$.', jsontable.keyname)) IS NULL)

id	jsontext
1	{"Test activityCompetence for createCompetence": 1}
2	{"Test": 3}
3	{"Test activityCompetence for createCompetence": 1}
4	{"blabla text": 2, "competenceForSearch": 20}
5	{"firstCompetence": 2, "fourthCompetence": 4}
6	{"firstCompetence": 2, "secondCompetence": 2}
7	{"one": 1, "two": 2, "three": 3}
8	{"one": 1, "two": 2, "four": 4, "three": 3}
9	{"one": 1, "four": 4, "three": 3}