How to extract key name from mysql's JSON object when searched value matches particular key's data?

I am trying to get key name and value from mysql's json object and limit the number of occurrence of a particular key.

This is what i have done so far:

Table Structure

id  history

History has following records

{"user":"a","msg":"Hello ba"}

{"user":"ba","msg":"bye"}

{"user":"bb","msg":"Hello CCa"}

{"user":"bc","msg":"Hello ka"}

Query

-- @num := if(@table_name = `table_name`, @num + 1, 1) as row_number,
   --  @table_name := `table_name` as dummy 
     
SELECT history, JSON_EXTRACT(history, "$.user"), JSON_KEYS(history)
     FROM chat_history
     WHERE JSON_EXTRACT(history, "$.user") like '%a%' or JSON_EXTRACT(history, "$.msg") like '%a%'

What i am getting now:

What i am expecting

1st Query Result

2nd Query Result (limit each key to 2 item)

Looking for your kind help. Thanks

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3iDZYej
via IFTTT