Things NOT to do with MySQL, generally speaking

string_manipulation_AND_json_output.sql

SELECT 
  ia.descriptor_id, descriptor_name, 
  MAX(pkg_override_id) AS pkg_id, MAX(version_type_id) AS version, 
  CASE WHEN MAX(version_type_id) > 1 THEN 1 ELSE 0 END AS overridden, 
  CONCAT('[',
    SUBSTRING_INDEX(
      GROUP_CONCAT( 
        CASE 
          WHEN cat_override_id IS NOT NULL AND cat_override_id > 0 
            THEN CONCAT('{"',ia.item_id,'" : [ {"iname" : "',item_name,'"} , {"icat" : "', 
                CASE 
                  WHEN cat_override_id IS NOT NULL OR cat_override_id = 0 
                    THEN cat_override_id 
                  ELSE -1 
                END,
              '"} ] }') 
          WHEN cat_override_id = 0 
            THEN CONCAT('{"',ia.item_id,'" : [ {"iname" : "',item_name,'"} , {"icat" : "', cat_override_id,'"} ] }///') 
          WHEN (pkg_override_id IS NOT NULL or pkg_override_id > 0) AND ia.version_type_id > 3 
            THEN CONCAT('{"',ia.item_id,'" : "',item_name,'"}///') 
          WHEN pkg_override_id IS NULL or pkg_override_id <= 0 
            THEN CONCAT('{"',ia.item_id,'" : "',item_name,'"}  ') 
          ELSE 0 
        END 
        ORDER BY ia.version_type_id DESC 
        SEPARATOR ',') 
      ,'///',1)
    ,']') AS powerlist 

FROM 
  type_item_associations pa, 
  type_items itm, 
  type_descriptors dd 
  
WHERE 
  (pkg_override_id IS NULL or pkg_override_id = 0 OR pkg_override_id = 261) 
  AND itm.item_id = ia.item_id AND dd.descriptor_id = ia.descriptor_id 
  AND ia.version_type_id IN(1,4,5) 
  
GROUP BY descriptor_id 

HAVING descriptor_id IN (26,6,23,27,19,3) 

ORDER BY descriptor_name