Untitled

SET @key := (
    SELECT `key`
    FROM (
        SELECT 
            JSON_UNQUOTE(JSON_EXTRACT(additional_information, CONCAT('$."', `key`, '".field'))) AS field,
            `key`
        FROM 
            jos_events_attendees,
            JSON_TABLE(
                JSON_KEYS(additional_information), 
                '$[*]' COLUMNS (`key` VARCHAR(255) PATH '$')
            ) AS jt
        WHERE JSON_UNQUOTE(JSON_EXTRACT(additional_information, CONCAT('$."', `key`, '".field'))) = 'zoom_registrant_id'
    ) AS subquery
    LIMIT 1
);

SELECT * FROM (
	SELECT 
		att.id,
		COALESCE (
			JSON_UNQUOTE(
		        JSON_EXTRACT(
		            JSON_EXTRACT(att.additional_information, '$[*].value'), 
		            '$[1]'
		        )
		    ),
		    JSON_UNQUOTE(
		        JSON_EXTRACT(att.additional_information, CONCAT('$."', @key, '".value'))
		    )
	    ) AS zoom_registrant_id
	FROM `jos_events_attendees` AS `att`
	JOIN `jos_events_reservations` AS `res` ON `res`.`id` = att.reservation_FK
) AS TBL
WHERE 
	zoom_registrant_id = 'Tf1aIsPHSY2P5DwkxUk1mg'
	-- res.event_FK = 1250
	-- AND att.additional_information->>'$.[*].field' = 'zoom_registrant_id'
    -- AND JSON_EXTRACT(att.`additional_information`, "$." 'zoom_registrant_id.+Tf1aIsPHSY2P5DwkxUk1mg\"\}'	
    -- AND att.`additional_information` REGEXP 'zoom_registrant_id.+Tf1aIsPHSY2P5DwkxUk1mg\"\}'
;