Query JSON data stored in a MySQL Database

By

Keiron

– 16/05/2011Posted in: MySQL

If you are storing JSON information in a MySQL database and need to perform a query based on this then you'll need this. Say you have a field in the database called extended and within it you store information for Company such as:

{"Company":"Sony","Country":"Japan"}

Then you will need the following to select the Company name from rows where the company name equals something.

$sql = "SELECT if(instr(extended,'Company'),substring(substring(extended,instr(extended,'Company')+10),1,instr(substring(extended,instr(extended,'Company')+10),'\",\"')-1),") FROM table WHERE if(instr(extended,'Company'),substring(substring(extended,instr(extended,'Company')+10),1,instr(substring(extended,instr(extended,'Company')+10),'\",\"')-1),")!="";

Like this? Maybe you could buy me a coffee.

Tags: json, MySQL, Query

About Keiron

Web Developer based in the UK. Click here if you want to work with me

4 Comments

1. Pa

   Posted June 21, 2011 at 5:14 PM

   This is exactly some thing I have to do more research into, many thanks for the publish.

2. David Barratt

   Posted July 13, 2011 at 2:55 PM

   hello!

   do you know if you can do ORDER BY when the data is stored in a json object? Par your example, what if you wanted to order the results by company name?

   thanks!
   david

3. Keiron

   Posted August 1, 2011 at 12:15 PM

   I haven't tried it, but I am pretty sure you could. The SQL is actually getting inside the field for the WHERE condition so ORDER BY should be no different.

4. Keiron

   Posted August 2, 2011 at 10:46 AM

   Actually, I have done it before:

   SELECT if(instr(extended,'Company'),substring(substring(extended,instr(extended,'Company')+10),1,instr(substring(extended,instr(extended,'Company')+10),'\"')-1),"),internalKey FROM modx_user_attributes WHERE if(instr(extended,'Company'),substring(substring(extended,instr(extended,'Company')+10),1,instr(substring(extended,instr(extended,'Company')+10),'\"')-1),")!=" ORDER BY if(instr(extended,'Company'),substring(substring(extended,instr(extended,'Company')+10),1,instr(substring(extended,instr(extended,'Company')+10),'\"')-1),") ASC