How can i extract all fields from Column using mysql query

The Data in the column is stored in this Format. I am storing Data in JSON format in column

{"item": {
    "actor":{
        "time":"2011-08-21 21:56:37",
        "ip":"127.0.0.1",
        "id":"2010072123522251",
        "email":"abc@abc.com",
        "session_id":"c05e3e61a19650b4ca12a21c2e34df5f",
        "loggedout":""},
    "action":{
        "name":"publish",
        "text":"a_id=11714" ,
"a_id":"11714"
    }
}}

Now i want the Result like this

开发者_高级运维time ip id email session_id

2011-08-21 21:56:37 127.0.0.1 2010072123522251 2010072123522251 2010072123522251

I used the query like this

SUBSTRING(msg,LOCATE('a_id',msg)+5,(LOCATE('"',msg)+2) ) AS answer_id

But it is not returning me good results. Some results are terminated. Please suggest

---

I don't have an answer for you but some thoughts occurred to me that I thought I might share.

Firstly, while I wouldn't recommend it, you could probably write a stored procedure in Java using one of the many JSON parsing libraries to parse the log entry. I don't think MySQL supports this but many other DBMS products (Postgres and Oracle for example) do. One of the many disadvantages is that you will always have to do a full table scan and logs can get very big.

Since you have figured out how to parse it with Python, you might consider doing something like this:

1. Parse the JSON into its constituent elements using python
2. Insert those elements into the database (properly normalised of course) along with the orginal JSON stored as a text BLOB for reference

Now you can do sensible queries like "show me log entries in this date range where the actor email was fred@gmail.com" and be able to take advantage of indexes etc. There's not much reason to retain the original JSON but you might just be glad of it some day when the format unexpectedly changes and you find that none of your parsing works.

Phil

---

I did it using Python text processing. as there is no good way via Sql.