Question 20404 in Mysql Question posted in
The official MySQL documentation can be found here.

Extracting python list values in MYSQL

I have a column in MySQL database which contains a python list of json formatted values like below:

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

I could not use json_extract() function because it’s not formatted exactly as a json

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

First_column Second_column
{"name":"me","color":"red"} {"name":"you","color":"blue"}
Tags:

2

Answers


  1. 0

    The following query with combination of string manipulation functions SUBSTRING_INDEX, REPLACE and CONCATshall get you the expected result.

    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 demo using DBFIDDLE

    This gives me expected output as :

    First_column Second_column
    {"name":"me","color":"red"} {"name":"you","color":"blue"}

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

    Login or Signup to reply.
  2. 0

    You should be able to use JSON_EXTRACT on the sample column included in your 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;

    Outputs:

    First_column Second_column
    {"name": "me", "color": "red"} {"name": "you", "color": "blue"}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search