Extract python list values ​​in MYSQL

Question

I have a column in a MySQL database that contains a python list of values ​​in json format, like this:

Column
[{"name":"me","color":"red"} , {"name":"you","color":"blue"}]

I cannot use the json_extract() function because its format is not exactly the same as json

I want to extract each json formatted in a new column like this:

First column	Second column
{"Name": "I", "Color": "Red"}	{"name":"you","color":"blue"}

Answer 1

The following query combined with the string manipulation functions SUBSTRING_INDEX, REPLACE, and CONCAT will yield the expected results.

SELECT 
  CONCAT('{', REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(`column`, '}', 1), '{', -1), '\"', '"'), '}') AS First_column,
  CONCAT('{', REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(`column`, '}', 2), '{', -1), '\"', '"'), '}') AS Second_column
FROM mytable;

Here is a working demonstration of using DBFIDDLE

This gives me the expected output:

first row	The second column
{"Name": "I", "Color": "Red"}	{"name":"you","color":"blue"}

Please replace mytable with your_actual_table_name and column with your actual column name. I surrounded the columns with backticks because column is a reserved keyword in sql.

Answer 2

You should be able to use JSON_EXTRACT on the example columns included in the question:

SET @column = '[{"name":"me","color":"red"} , {"name":"you","color":"blue"}]';

SELECT
    JSON_EXTRACT(@column, '$[0]') AS First_column,
    JSON_EXTRACT(@column, '$[1]') AS Second_column;

Output:

first row	The second column
{"Name": "I", "Color": "Red"}	{"Name": "You", "Color": "Blue"}