Menu
I have a MySQL table with a JSON column called sent. The entries in the column have information like below:
{
"data": {
"12":"1920293"
}
}
I'm trying to use the mysql query:
select sent->"$.data.12" from mytable
but I get an exception:
Invalid JSON path expression. The error is around character position 9.
Any idea How I can extract the information? The query works fine for non-numeric subfields.
848
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).
In MySQL, JSON values are written as strings. MySQL parses any string used in a context that requires a JSON value, and produces an error if it is not valid as JSON.
@Ibrahim, You have an error in your code. If you use number (or spaced words) as key in a JSON data type in MySQL, you'll need to double-quote it.
Therefore, the correct MySQL statement in your case is:
select sent->'$.data."12"' FROM mytable;
Thanks, @JeffreyKilelo
150
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
