Comments - JSON_KEYS

Is it possible to split the result of JSON_KEYS into several rows ? We are trying to move our database from PostgreSQL to MariaDB, and over there we could use json_object_keys to do so.

There are a few ways of doing it. Here are a few examples that I came up with:

-- This is our JSON data, could be inlined in the SQL as well
SET @json_doc='{"A": 1, "B": {"C": 2}, "D": 3}';

-- With a Recursive Common Table Expression
WITH RECURSIVE data AS (
  SELECT JSON_VALUE(JSON_KEYS(@json_doc), '$[0]') AS v, 0 AS idx
  UNION
  SELECT JSON_VALUE(JSON_KEYS(@json_doc), CONCAT('$[', d.idx + 1, ']')) 
  AS v, d.idx + 1 AS idx FROM data AS d
  WHERE d.idx < JSON_LENGTH(JSON_KEYS(@json_doc)) - 1
) SELECT v FROM data;

-- With the Sequence engine (the upper limit is hard-coded)
SELECT JSON_VALUE(JSON_KEYS(@json_doc), CONCAT('$[', seq, ']')) FROM seq_0_to_100000000 WHERE seq < JSON_LENGTH(JSON_KEYS(@json_doc));