I have a column in my database which is called user_log. It basically logs everything a user does on the page. I use MySQL.

It looks like that:

Now, I want to extract all "last_login" and get the value of it. It is a text field, but not a dict or something else

CodePudding user response：

You can use json_extract in MySQL. SELECT user_id, json_extract(user_log, '$.last_login') as last_login FROM users;

Sample query: https://onecompiler.com/mysql/3y8a3brhr

More on json_extract here in the mariadb docs: https://mariadb.com/kb/en/json_extract/

CodePudding user response：

You can simply use JSON_VALUE() function provided that the DB is of version 8.0.21 such as

SELECT JSON_VALUE(user_log, '$.last_login') AS extracted_value
  FROM t