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:
| user_id | user_log |
|---|---|
| 1028 | { "last_login":"2022-04-08 12:03:05", "blog_entry_at":"2022-04-08 12:43:12" } |
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
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/
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