searching for a string value pair in json object using mysql

Question

I have a json object in my database table field like following :

[{"user_id":"xyz","viewed":"false","answered":"false","denied":"false"}]. the other fields are id, q_id and stuff.

I want to search for the user xyz in the table!how can i do that using mysql?

search for the user could you please elaborate a bit on it ? — Abhik Chakraborty
– Abhik Chakraborty, Commented Nov 25, 2014 at 7:25
i want to search for xyz in the above object! How can i accomplish that? — Ajay Ganvir
– Ajay Ganvir, Commented Nov 25, 2014 at 7:26
still did not get.. so your input value will be xyz and you want to see if its there in the object which is stored in database ? — Abhik Chakraborty
– Abhik Chakraborty, Commented Nov 25, 2014 at 7:30

Manwal · Accepted Answer · 2014-11-25 07:32:11Z

1

JSON Parsing in MySQL Using Common_schema

Try following query using common schema

select * from tablename where common_schema.extract_json_value(tablename.columnName,'/user_id') = 'xyz';

Reference: http://mechanics.flite.com/blog/2013/04/08/json-parsing-in-mysql-using-common-schema/

answered Nov 25, 2014 at 7:32

Manwal

23.9k15 gold badges65 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Abhik Chakraborty · Accepted Answer · 2014-11-25 07:48:45Z

1

If the object pattern is same across then you can use the substring_index function to parse the data, below is the example of finding the user_id from this pattern

mysql> select replace(substring_index(substring_index('[{"user_id":"xyz","viewed":"false","answered":"false","denied":"false"}]','"user_id":',-1),',',1),'"','') as user_id;
+---------+
| user_id |
+---------+
| xyz     |
+---------+

Now if you want to select all the rows having user_id = xyz you can use the above as

select * from table_name 
where replace(substring_index(substring_index('[{"user_id":"xyz","viewed":"false","answered":"false","denied":"false"}]','"user_id":',-1),',',1),'"','') = 'xyz';

answered Nov 25, 2014 at 7:48

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

3 Comments

Clippy2k Over a year ago

Thanks for this, helped reduce query times by 400ms on a ~10k row table! much more efficient than using LIKE as the (self answered) "Correct" answer does!

Abhik Chakraborty Over a year ago

It could reduce the time, however thats not an efficient way of storing the user_id like this as a flat JSON, rather the application level should parse the user_id before storing into the DB and then by using the column index it could be far far bettter.

Clippy2k Over a year ago

100% agree, but working with a database tied into a black box system here, unfortunately, these things have to be done!

Ajay Ganvir · Accepted Answer · 2014-11-25 11:57:48Z

1

thank you for your answers. But i just used a like query and it worked

SELECT viewers from tablename where viewers like '%\"user_id\":\"xyz\"%' && qid= 1;

answered Nov 25, 2014 at 11:57

Ajay Ganvir

4772 gold badges8 silver badges23 bronze badges

Comments

vinay rajan · Accepted Answer · 2014-11-25 07:26:30Z

0

In mysql there is a functionality for dba called common schema. It has json functionality. Or use find_in_set() function to find coma seperated values in json.

answered Nov 25, 2014 at 7:26

vinay rajan

3614 silver badges9 bronze badges

1 Comment

vinay rajan Over a year ago

SELECT IF(FIND_IN_SET('"viewed":"false"','[{"user_id":"xyz","viewed":"false","answered":"false","denied":"false"}]'),1,0) AS z

Collectives™ on Stack Overflow

searching for a string value pair in json object using mysql

4 Answers 4

Comments

3 Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related