MySQL: Extract all Keys from a nested JSON String

Question

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

I think you might need a recursive CTE to do this. It will be easier in a real programming language, then you can write a recursive function. — Barmar
– Barmar, Commented Feb 8, 2022 at 9:18

Akina · Accepted Answer · 2022-02-08 10:37:33Z

1

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

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

db<>fiddle here

answered Feb 8, 2022 at 10:37

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Collectives™ on Stack Overflow

MySQL: Extract all Keys from a nested JSON String

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