mysql提取拼接

common_attributes_json=[{"key":"工艺种类","value":"PVC覆膜","valueTypeStr":"text","required":true,"allowCustomization":false,"isSalesAttribute":true},{"key":"国产/进口","value":"国产","valueTypeStr":"text","required":true,"allowCustomization":false,"isSalesAttribute":false}]

求助的gpt

SELECT id,common_attributes_json
,
  (SELECT 
     GROUP_CONCAT(
       CONCAT(
         JSON_UNQUOTE(JSON_EXTRACT(common_attributes_json, CONCAT('$[', idx, '].key'))), 
         ': ', 
         JSON_UNQUOTE(JSON_EXTRACT(common_attributes_json, CONCAT('$[', idx, '].value')))
       ) SEPARATOR ', '
     ) 
   FROM 
     (SELECT 
        (@idx := @idx + 1) AS idx
      FROM 
        (SELECT @idx := -1) vars,
        sitem.item
   ) AS subquery2
   WHERE 
     idx < JSON_LENGTH(common_attributes_json)
  ) AS ab
FROM 
  sitem.item limit 10;

image.png