perform an sql query on json_encoded data like filter

Question

I have a table which has a field called tags which is of type varchar. this column holds json_encoded data.

["4","5","6","7"]

a record can have multiple tags. I don't wanna to resort to using pivot table which I know is more efficient. I want to keep it this way at the moment.

i want to filter all the records which has specific tag. just a single tag... How can i create a query for that?

$cats = $_POST['cats']; if(! $cats==0) { $filters .= 'AND category` LIKE %'. $cats . '%'; }` — cabs
– cabs, Commented Jun 16, 2016 at 5:28
This code is invalid, could that be your problem? The query itself looks good enough to return any tags that is a permutation of $cats — Stian
– Stian, Commented Jun 16, 2016 at 5:31

Stian · Accepted Answer · 2016-06-16 05:28:54Z

1

SELECT * FROM WHERE json_encoded_list_of_tags LIKE '%TAG%'

Assuming you only need the one tag the %'s are wildcards (as you probably know?) and this will match any row that has TAG in the column.

you should also refine this query to include escaped quotation marks, to avoid matching with substrings of other tags

answered Jun 16, 2016 at 5:28

Stian

6954 silver badges12 bronze badges

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

Collectives™ on Stack Overflow

perform an sql query on json_encoded data like filter

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related