Geek Answers Handbook

MySQL query to find a field using a JSON string

It may be simple enough with basic SQL or REGEXP may be required, but I hit a brick wall.

My data is stored in a JSON string, like these two examples (each in 1 field):

  [{"id": "2", "value": ["1", "3"]}, {"id": "3", "value": ["1", "2"]}]

and

  [{"id": "3", "value": ["2"]}, {"id": "3", "value": ["1", "2", "5"]}]

I want to find the meanings between these last brackets, which can consist of many numbers ["1","2","5"] or just a single on ["2"] . The initial digits correspond to two categories - single "id":"2" and "id":"3" .

Using %"2"% with a simple LIKE certainly fits all. I can request "id":"$var" to return each category, and then use PHP to filter it after we get the results, but the data can be quite large, and I'm sure this is easy for the SQL guru .

I have no way to change the format of the field, it should remain as JSON.

Any help appreciated! Thanks.

+6
sql regex mysql
source share
2 answers

I think I solved it using this: AND extra_fields REGEXP '(.*"id":"2".*)("\[.*"1".*\]")' . This has more to do with regular expressions than with MySQL: P

comment: (I could not find the comment button)
This syntax becomes clearer when you find out that "extra_fields" is the name of a column in a table

+14
source share

In SQL, this is really simple because you only need to identify the last opening bracket. This can be done with the substring index.

Maybe your table is called test, and your field with a row called text should work

 select text from (select substring_index(text,'[',-1) as text from test) as new where text like '%5%'**

If you have text between two unique delimiters, you might find this tutorial useful.

0
source share

More articles:


All Articles