Search code examples
mysqlsqljsonstring-concatenation

mysql json_extract dynamic position

SELECT id, JSON_EXTRACT(column,'$.parent[3].child') AS child FROM table 
WHERE JSON_EXTRACT(column,'$.parent[3].child') IN (1)

this is my sql query. i have a also column jsonlength how can i connect this 2 columns in one query ? i want to change 3 to dynamic number given in jsonlength

for example:

SELECT id, JSON_EXTRACT(column,'$.parent['jsonlength'].child') AS child FROM table 
WHERE JSON_EXTRACT(column,'$.parent['jsonlength'].child') IN (1)

but this query not working.

Solution

  • You need to form the JSON path with string concatenation. In MySQL, this is done with the CONCAT() function.

    SELECT id, JSON_EXTRACT(column, CONCAT('$.parent[',jsonlength,'].child')) AS child FROM table 
WHERE JSON_EXTRACT(column,CONCAT('$.parent[',jsonlength,'].child')) IN (1)