Comments - JSON_KEYS

4 years, 12 months ago Radhi Fadlillah

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.

 
4 years, 11 months ago Markus Mäkelä

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));
 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.