How to extract values from a numeric-keyed nested JSON field in MySQL

Question

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.

@wchiquito it doesn't work. it consider string ' "12" ' as key — moghya
– moghya, Commented Mar 29, 2018 at 13:38
This seems to be bug in mysql engine as I'm getting Error: UNKNOWN_CODE_PLEASE_REPORT: here db-fiddle.com/f/bLUtMxWwAH9A2hT7LEaATS/0 — moghya
– moghya, Commented Mar 29, 2018 at 14:20

Jeffrey Kilelo · Accepted Answer · 2018-03-29 15:06:27Z

23

@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

answered Mar 29, 2018 at 15:06

Jeffrey Kilelo

6,8732 gold badges13 silver badges7 bronze badges

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

1 Answer 1