Get element value in a mysql nested json data

Question

What would be the right way of getting Ajax, i.e. the value for the last occurence for key child1Dob1, from a json field that has a data structure that looks like the below,

{
  "data": {
    "data": {
      "data": {
        "child1Dob1": "Andy"
      },
      "child1Dob1": "Bob"
    },
    "child1Dob1": "Rick"
  },
  "child1Dob1": "Ajax"
}

The below query was an attempt from a similar question but i am getting a null value, so obviously i am missing something.

SELECT JSON_EXTRACT(`containerValue`,CONCAT("$.data[",JSON_LENGTH(`containerValue` ->> '$.data')-1,"]")) from myTable where containerKey = 'theContainer';

Answer 1

For CREATE TABLE test (data JSON):

WITH RECURSIVE 
cte AS (
  SELECT data, data -> '$.data' subdata
  FROM test
  UNION ALL
  SELECT subdata, subdata -> '$.data' 
  FROM cte
  WHERE subdata IS NOT NULL
)
SELECT data ->> '$.child1Dob1'
FROM cte
WHERE subdata IS NULL;