Extract all objects from json array using MariaDB 10.3

Question

How can I extract every object from this json array using MariaDB 10.3? I think the JSON_TABLE function in MySQL does what I need, but I'm currently constrained to MariaDB.

SET @json_doc='[
 {
 "DatabaseName": "master",
 "Schema Name": "dbo",
 "Object Name": "CommandLog",
 "column_index_id": 1,
 "Buffer size(MB)": 0.02,
 "BufferCount": 2,
 "Row Count": 3,
 "Compression Type": "NONE"
 },
 {
 "DatabaseName": "master",
 "Schema Name": "dbo",
 "Object Name": "xevents",
 "column_index_id": 0,
 "Buffer size(MB)": 0.16,
 "BufferCount": 21,
 "Row Count": 0,
 "Compression Type": "NONE"
 }
]';

With this query I can get the one of the objects in the array, but not all. There won't always be the same number of objects in the array so I need it to be dynamic.

WITH RECURSIVE data AS (
 SELECT 
 JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),'$[0]') AS json_column, 
 JSON_VALUE(@json_doc,CONCAT('$[0].',JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),'$[0]'))) AS json_data, 
 0 AS column_index
 UNION
 SELECT 
 JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'), CONCAT('$[', d.column_index + 1, ']')) AS json_column, 
 JSON_VALUE(@json_doc,CONCAT('$[0].',JSON_VALUE(JSON_KEYS(@json_doc,'$[0]'),CONCAT('$[', d.column_index + 1, ']')))),
 d.column_index + 1 AS column_index
 FROM data AS d
 WHERE d.column_index < JSON_LENGTH(JSON_KEYS(@json_doc,'$[0]')) - 1
)
SELECT json_column, json_data, column_index
FROM data;

This is the result I currently get.

------------------------------------------
|json_column |json_data |column_index |
|DatabaseName |master |0 |
|Schema Name |dbo |1 |
|Object Name |CommandLog|2 |
|column_index_id |1 |3 |
|Buffer size(MB) |0.02 |4 |
|BufferCount |2 |5 |
|Row Count |3 |6 |
|Compression Type|NONE |7 |
-------------------------------------------

This is the result I desire.

-------------------------------------------------------
|json_column |json_data |column_index |array_index|
|DatabaseName |master |0 |0 |
|Schema Name |dbo |1 |0 |
|Object Name |CommandLog|2 |0 |
|column_index_id |1 |3 |0 |
|Buffer size(MB) |0.02 |4 |0 |
|BufferCount |2 |5 |0 |
|Row Count |3 |6 |0 |
|Compression Type|NONE |7 |0 |
|DatabaseName |master |0 |1 |
|Schema Name |dbo |1 |1 |
|Object Name |xevents |2 |1 |
|column_index_id |0 |3 |1 |
|Buffer size(MB) |0.16 |4 |1 |
|BufferCount |21 |5 |1 |
|Row Count |0 |6 |1 |
|Compression Type|NONE |7 |1 |
|... |... |... |n |
-------------------------------------------------------

Answer 1

You can accomplish this by using a dedicated numbers_table. This is a table with a single column number and can have as many values as you require (I have 0 to 999)

For your question, there are two scenarios: The first is if you do not know the keys, or they change. The second is if you do know the keys and in this case you can get each key of the json objects as a column.

Scenario 1:

SELECT 
 JSON_VALUE(JSON_KEYS(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$[',n2.number,']')) AS json_column,
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$.',JSON_VALUE(JSON_KEYS(@json_doc,CONCAT('$[',n1.number,']')),CONCAT('$[',n2.number,']')))) AS json_data,
 n2.number AS column_index,
 n1.number AS array_index
FROM numbers_table AS n1
JOIN numbers_table AS n2
WHERE 
 n1.number < json_length(@json_doc) AND
 n2.number < json_length(json_keys(@json_doc,CONCAT('$[',n1.number,']')));

-------------------------------------------------------
|json_column |json_data |column_index |array_index|
|DatabaseName |master |0 |0 |
|Schema Name |dbo |1 |0 |
|Object Name |CommandLog|2 |0 |
|column_index_id |1 |3 |0 |
|Buffer size(MB) |0.02 |4 |0 |
|BufferCount |2 |5 |0 |
|Row Count |3 |6 |0 |
|Compression Type|NONE |7 |0 |
|DatabaseName |master |0 |1 |
|Schema Name |dbo |1 |1 |
|Object Name |xevents |2 |1 |
|column_index_id |0 |3 |1 |
|Buffer size(MB) |0.16 |4 |1 |
|BufferCount |21 |5 |1 |
|Row Count |0 |6 |1 |
|Compression Type|NONE |7 |1 |

Scenario 2:

SELECT 
 n1.number AS array_index,
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.DatabaseName') AS 'DatabaseName',
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Schema Name') AS 'Schema Name',
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Object Name') AS 'Object Name',
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.column_index_id') AS 'column_index_id',
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Buffer size(MB)') AS 'Buffer size(MB)',
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.BufferCount') AS 'BufferCount',
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Row Count') AS 'Row Count',
 JSON_VALUE(JSON_QUERY(@json_doc,CONCAT('$[',n1.number,']')),'$.Compression Type') AS 'Compression Type'
FROM numbers_table AS n1
WHERE 
 n1.number < json_length(@json_doc);

Output:

|array_index|DatabaseName|Schema Name|Object Name|column_index_id|Buffer size(MB)|BufferCount|Row Count|Compression Type|
|0 |master |dbo |CommandLog |1 |0.02 |2 |3 |NONE |
|1 |master |dbo |xevents |0 |0.16 |21 |0 |NONE |

• Mike F.: The definition of the numbers table can be omitted using WITH RECURSIVE statement. See examples section of MariaDB docs, Scenario 2 example dbfiddle