Similar to JSON_TABLE in MYSQL so keys are one of the columns?

Question

MySQL

In some scenarios, I need to build a table from a column with json data.
For example, here is an object: {"1": 2, "2": 4, "3": 1, "4": [2, 3, 6]}. All keys are unique among themselves. There are no nested objects.
The easiest way is to build such a table through their native commands:

1 2
2 4
3 1
4 2
4 3
4 6

When using JSON_TABLE, work is carried out only with values, and the keys, on the contrary, must be non-unique, that is, to build a table through such a function, you need to have the following source array:
[{"key": 1, "value: 2}, {"key": 2, "value": 4}].

Somehow it is possible to construct the table with unique keys easier? I didn't find the command as simple and can be said to be the reverse of the JSON_OBJECTAGG command. I need to execute a command in reverse of JSON_OBJECTAGG. Column1 is the key, column2 is the value.

Is it really necessary to work in a loop with each pair through JSON_KEY and JSON_EXTRACT, and only form table rows in this way?

Answer 1

Is it really necessary to work in a loop with each pair through JSON_KEY and JSON_EXTRACT, and only form table rows in this way?

Since such a data structure is chosen in JSON, then using SQL is the only way.
Try

[{"key": 1, "value: 2}, {"key": 2, "value": 4}]

This structure allows you to use JSON_TABLE. Then you get a list (id, key, value) with the same id.
However, if based on the task How to build a database architecture, where users have many fields? If you want to get a list of questions with their answers by user_id, then it is easier to decode JSON on the DBMS client side, in the program itself, entering it into the appropriate models, without using JSON_TABLE.

Answer 2

12.17.6 JSON Table Functions
This section contains information about JSON functions that convert JSON data to tabular data. In MySQL 8.0.4 and later, one such function—JSON_TABLE()—is supported.

Answer 3

All keys are unique to each other

This is an unnecessary clarification, because in object, all keys are unique by definition. In principle, there are no identical keys.
As for simplicity, json itself is a simple format, but not the scenarios in which it can be applied. What you wrote is not a standard way of working with Json, which is included in the set of its basic functions - such as getting keys or value by key or element array length. Therefore, even the script for obtaining such a "key-value" list, as you should write. Alas.