Menu
This might be simple enough with basic SQL or it might need a REGEXP but I've 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 search for values in between those last brackets which might consist of many numbers ["1","2","5"] or just a single on ["2"]. The beginning numbers correspond to 2 categories - the single "id":"2" and "id":"3".
Using %"2"% with a simple LIKE of course matches everything. I can query by the "id":"$var" to return each category then use PHP to filter it through after we have the results, but the data can be quite large and I'm sure it's easy for a SQL guru.
I don't have the option to change the format of the field, it has to remain as JSON.
Any help appreciated! Thanks.
203
MySQL provides two operators ( -> and ->> ) to extract data from JSON columns. ->> will get the string value while -> will fetch value without quotes. As you can see ->> returns output as quoted strings, while -> returns values as they are. You can also use these operators in WHERE clause as shown below.
To query JSON data, you can use standard T-SQL. If you must create a query or report on JSON data, you can easily convert JSON data to rows and columns by calling the OPENJSON rowset function. For more information, see Convert JSON Data to Rows and Columns with OPENJSON (SQL Server).
LOCATE() function MySQL LOCATE() returns the position of the first occurrence of a string within a string. Both of these strings are passed as arguments. An optional argument may be used to specify from which position of the string (i.e. string to be searched) searching will start.
In MySQL 8.0. 21 and later, it is also possible to create an index on a JSON column using the JSON_VALUE() function with an expression that can be used to optimize queries employing the expression.
I think I solved it by using this: AND extra_fields REGEXP '(.*"id":"2".*)("\[.*"1".*\]")'. It's more to do with regular expressions than it is to do with MySQL :P
comment: (I couldn't find comment button)
This syntax becomes clearer when you learn that "extra_fields" is the name of the column in the table
105
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
