0

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`

1
  • 1
    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.
    – Akina
    Jun 2, 2022 at 9:23

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.