not self explicable 1064 error trying to use MEMBER OF in a mysql query-CodePudding

I have a query where I select some rows of a table based on their ID.

SELECT id,hotel_name FROM hotel WHERE id IN (1,2) and this works fine. It returns two rows from hotel, id 1 and id 2 as expected. IDs in the where clause are hardcoded but data are coming from another table where they are stored in a json column as ["1","2"]. On a normal table i'd have done I'd have done (WHERE id in (SELECT ...) so I tried to replace the IN operator whith MEMBER OF that should behave like IN for JSON columns.

SELECT id,hotel_name FROM hotel 
WHERE id MEMBER OF (
    SELECT details FROM auth_events 
    WHERE auth_events.id_user=1 AND auth_events.id_auth_catalog=10 
);

but I get

Syntax error in the query next to 'SELECT details FROM auth_events WHERE auth_events.id_user=1 AND auth_events.id_a'

but I don't understand exactly what is raising it. The inner query SELECT details FROM auth_events WHERE auth_events.id_user=1 AND auth_events.id_auth_catalog=10 is returning correctly the ["1","2"] array.

Can anyone help me understand what am I doing wrong? Also note that my mysql version is 8.0.26 and MEMBER OF is available since 8.0.13 so this shouldn't be the point

CodePudding user response：

The operand of MEMBER OF must be scalar JSON array, not a rowset.

Use

SELECT id,hotel_name 
FROM hotel 
JOIN auth_events ON auth_events.id_user=1 
                AND auth_events.id_auth_catalog=10
                AND hotel.id MEMBER OF (auth_events.details);

PS. The MEMBER OF() operator was added in MySQL 8.0.17, not in 8.0.13.