mysql - Search JSON_ARRAY using dynamic variable

Question

Is it possible to search a JSON_ARRAY using a variable as the index as opposed to hard coding a value in?

Here's what I mean:

SELECT
t1.*,
t1.tickets->>"$[t1.arr_pos]"
FROM
(
    SELECT
    c.id AS competition_id,
    JSON_ARRAYAGG(t.id) AS tickets,
    COUNT(t.id) AS tickets_sold,
    FLOOR(RAND()*(COUNT(t.id)-0+1)) AS arr_pos
    FROM competitions c
    JOIN tickets t ON t.competition_id = c.id
    WHERE c.end_date = '2021-01-15 15:00:00'
    AND c.tickets_sold > 0
    GROUP BY c.id
) t1

If I change t1.arr_pos to a number (0, 1, 2, etc) it obviously works but I need to search based on what is contained in arr_pos.

question from:https://stackoverflow.com/questions/65643262/search-json-array-using-dynamic-variable

Answer 1

Couldn't get JSON_SEARCH to work but what did work was the following:

SELECT
t1.*,
JSON_EXTRACT(t1.tickets, CONCAT('$[', t1.arr_pos, ']')) AS winning_ticket
FROM
(
    SELECT
    c.id AS competition_id,
    JSON_ARRAYAGG(t.id) AS tickets,
    COUNT(t.id) AS tickets_sold,
    FLOOR(RAND()*(COUNT(t.id))) AS arr_pos
    FROM competitions c
    JOIN tickets t ON t.competition_id = c.id
    WHERE c.end_date = '2021-01-15 15:00:00'
    AND c.tickets_sold > 0
    GROUP BY c.id
) t1