Mysql WHERE field containing JSON variable

Question

I have a database having a filed myfield containing json string:["2","4","1","6"] OR ["1"] . I want to make a filter for this filed and was thinking to query it with:

SELECT id FROM table_name WHERE myfield = '5' -> this return can not echo id because ["2","4","1","6"] does not have the value 5.

SELECT id FROM table_name WHERE myfield = '1' -> return can echo id-> because -> in json string on filed myfield have the value 1.

how can fix it? what do i do?

I tried as but it don't work for me:

SELECT id FROM table_name WHERE myfield = '1'

juergen d · Accepted Answer · 2013-02-21 13:49:52Z

1

To check for id=1 for instance you can do

SELECT 1 as id
FROM table_name 
WHERE find_in_set('1', replace(myfield, '"', '')) > 0

You can use it like this in your code

$this->db->query("SELECT 1 as id 
                  FROM table_name 
                  WHERE find_in_set('1', replace(myfield, '\"', '')) > 0");

edited Feb 21, 2013 at 13:49

answered Feb 21, 2013 at 12:08

juergen d

205k40 gold badges303 silver badges377 bronze badges

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

8 Comments

Taylor Gomez Over a year ago

I use it on codeigniter as: $row = $this->db->query('SELECT id FROM table_name WHERE find_in_set(5, replace(myfield, '"', '')) > 0')->row(); and echo result as: echo $row->fieldname; and getting this error:Parse error: syntax error, unexpected '1' (T_LNUMBER) what do i do?

juergen d Over a year ago

I don't know. I see no 1 in your query. Do you have an id column in your table?

juergen d Over a year ago

BTW you should delimit your strings with " instead of ': $row = $this->db->query("SELECT...

juergen d Over a year ago

Delimit your query with " as I said. If you don't then you will end the query with the next '

Taylor Gomez Over a year ago

I use of " it interferes with ..., '"', ... and have error: Parse error: syntax error, unexpected '', '' (T_CONSTANT_ENCAPSED_STRING)????

|

Manoj Dhiman · Accepted Answer · 2015-12-18 07:31:38Z

0

The given solution by @juergen will not work if you search first or last item . For this you need to replace square brackets also . This worked for me

SELECT * 
FROM `tbl_tasks_weekly` 
WHERE FIND_IN_SET('1', REPLACE( REPLACE( REPLACE(`myfield`,']','') ,  '[',''),'\"',''))

answered Dec 18, 2015 at 7:31

Manoj Dhiman

5,1616 gold badges32 silver badges71 bronze badges

Comments

SuReSh · Accepted Answer · 2016-02-18 10:11:25Z

0

Use serialization and deserialization to save or retrieve data from database field.

answered Feb 18, 2016 at 10:11

SuReSh

1,5111 gold badge22 silver badges48 bronze badges

Collectives™ on Stack Overflow

Mysql WHERE field containing JSON variable

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related