Mariadb – Extract all objects from json array using MariaDB 10.3

jsonmariadbmariadb-10.3select

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          |
-------------------------------------------------------

Best Answer

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            |
Related Question