CODEWITHSUNDEEP
mysqlsqlkey
nawab
nawab

Reputation: 53

SQL get value from key in dictionary

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

Upvotes: 2

Views: 2828

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

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

Upvotes: 1

Elias Amha
Elias Amha

Reputation: 349

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/

Upvotes: 2

Related Questions