Extract python list values in MYSQL-PHP Chinese Network Q&A

Article Topic Learning Download Q&A Programming Dictionary Game Recent Updates

简体中文(ZH-CN) English(EN) 繁体中文(ZH-TW) 日本語(JA) 한국어(KO) Melayu(MS) Français(FR) Deutsch(DE)

Extract python list values in MYSQL

P粉153503989 2023-09-04 19:35:45

437

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"}

P粉153503989

reply all (2)

P粉3116177632023-09-05 18:49:35 2 floor

The following query combined with the string manipulation functionsSUBSTRING_INDEX,REPLACE, andCONCATwill 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 aworking demonstration of using DBFIDDLE

This gives me the expected output:

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

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

Like+0

Add Reply

P粉4457144132023-09-05 17:11:12 1 floor

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: