CODEWITHSUNDEEP
mysqljson
Blob
Blob

Reputation: 381

MySQL: Extract all Keys from a nested JSON String

In addition to this answer is it possible to extract nested keys in a simple way? Example:

{
  "a": value,
  "b": {
    "c": value
    "d": {
      "e": value
   } 
  }
}

Expected output: ['a', 'b.c', 'b.d.e'] What I have tried:

 SELECT 
 f.`id` AS `field_name`
 
 FROM table t,
    JSON_TABLE(
         JSON_KEYS(t.`column`, '$.b'),
         '$[*]' COLUMNS(
           `id` VARCHAR(191) PATH '$'
         )
     ) AS t

but that would only show me one of the nested keys and skip the outer

Upvotes: 1

Views: 872

Answers (1)

Akina
Akina

Reputation: 42764

SELECT JSON_SEARCH(val, 'all', 'value') result
FROM test;

| result                      |
| :-------------------------- |
| ["$.a", "$.b.c", "$.b.d.e"] |

db<>fiddle here

Upvotes: 1

Related Questions