Specify range of Json array from different column



  • Mysql 8 makes it possible to extract part of an array saved in a JSON column as shown below:

    SELECT jcol->>'$[1 to 5]' FROM `db`.`table` which 1 and 5 are boundaries of the desired range of elements in array.

    My question is how to specify the range by numbers from different column. Some thing like this: SELECT jcol->>'$[table2.from to table2.to]' FROM `db`.`table`



  • You must build the JSON path using common string processing.

    SELECT JSON_UNQUOTE(JSON_EXTRACT(jcol, CONCAT('$[', table2.from, ' to ', table2.to, ']'))) 
    FROM db.table
    

    The JSON path is literal and it is not evaluated as an expression, so you must use CONCAT() for the path building.

    And the expression usage does not allow to use ->> operator, you must use JSON_UNQUOTE(JSON_EXTRACT()) instead.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2