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';

*the last occurence for child1Dob1* What is "the last"? the object properties are not ordered (rather than array elements). The most inner? use recursive CTE.. — Akina, Apr 15 '22 at 06:03
correct, the most inner would be another way of describing it — Ayubx, Apr 15 '22 at 06:05

score 1 · Answer 1 · answered Apr 15 '22 at 06:12

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;

Get element value in a mysql nested json data

1 Answers1