JSON Array and simple data in the same column-CodePudding

I work on a mySQL db with a column consisted of simple data and JSON array. How should i implement the SELECT to retrieve the values ?? Check the screenshot

CodePudding user response：

While you dont give enough information, i suppose you at least have a corresponding table and you can get the row id of the phone that you want to pull. With that in mind

--also assuming that the table is called phones
SELECT * FROM PHONES;
SET @result := (SELECT phone FROM PHONES WHERE Id = 2);
SELECT IF(JSON_VALID(@result) = 1, json_extract(@result,'$[0]'), @result) AS Result;

What this does is that it checks if the entry is valid json, and if it does it extracts the first phone number. if its not, then it returns the original result.

if you want to return all phone numbers then an additional query is needed to find how long in that json

json_array_length('[1,2]') → 2

Then the CONCAT function to concatenate the phone numbers.