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
– Akina, Commented Apr 15, 2022 at 6:03
correct, the most inner would be another way of describing it — Ayubx
– Ayubx, Commented Apr 15, 2022 at 6:05

Akina · Accepted Answer · 2022-04-15 06:12:47Z

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;

answered Apr 15, 2022 at 6:12

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Get element value in a mysql nested json data

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related