Using left join to query and match JSON field in MySQL

Table A,field : pay_type_fk,type:json, Examples Data: [1,2,4] Table B,field : id type : bigint Examples Data: primary key

SQL I use:

SELECT
    pay_payment_type.id,
    pay_payment_type.pay_name,
    pay_payment_type.configuration,
    pay_payment_type.pay_function_name,
    GROUP_CONCAT( pay_type.MODE ) 
FROM
    pay_payment_type
    LEFT JOIN pay_type ON pay_type.id = CAST( JSON_EXTRACT( pay_payment_type.pay_type_fk, '$[0]' ) AS UNSIGNED ) 
GROUP BY
    pay_payment_type.id

Only pay can be found_ For the first record in the type table, I think we can query all of them with group_ CONCAT

Answer

If I follow you correctly, you can join on JSON_CONTAINS():

select
    ppt.id,
    ppt.pay_name,
    ppt.configuration,
    ppt.pay_function_name,
    group_concat(pt.mode) as pay_type_modes 
from pay_payment_type ppt
left join pay_type pt on json_contains(ppt.pay_type_fk, cast(pt.id as char))
group by ppt.id

That said, a better option would be to fix your design. Instead of using a JSON array to store multiple dependent keys, you should have a separate bridge table to represent this many-to-many relationship, with each value in a separate row.