假设在mysql数据库表中有一个名为arbitaryjsonvalues的JSON列，该列具有以下值

arbitraryjsonvalues
===================
Dickers,
45,
true,
false,
null,
["a", "man", "book", "girl", "cat"],
["fish", "dog", "cat"],
{
   "foo": {
     "boo": [
       "babe1", "babe2", "babe3"
     ],
     "big": {
       "cat": 16,
       "dog": 90
     },
     "small": ["paradise", "hell"]
   },
   "goo": "heavens gate",
   "yeah": "rice pot on fire"
},
{
   "foo": {
     "lover": [
       "lover1", "lover2", "lover3"
     ],
     "big": {
       "cylinder": 16,
       "cone": 90
     },
     "small": ["banner", "logo"]
   },
   "yeah": "pizza on the table"
}

我正在尝试构造一个查询，将所有这些JSON值合并到一个数组中，结果应该是以下之一

Result 1
 ==================
 [
   "a", 
   "man", 
   "book", 
   "girl", 
   "cat", 
   "fish", 
   "dog", 
   "Dickers", 
   45, 
   true, 
   false,
   {
     "foo": {
       "lover": [
         "lover1", "lover2", "lover3"
       ],
       "boo": [
         "babe1", "babe2", "babe3"
       ],
       "big": {
         "cylinder": 16,
         "cone": 90,
         "cat": 16,
         "dog": 90
       },
       "small": ["paradise", "hell", "banner", "logo"]
     },
     "goo": "heavens gate",
     "yeah": ["rice pot on fire", "pizza on the table"]
   }
 ]

 Result 2
 ==================
 [
   [],
   {
     "foo": {
       "lover": [],
       "boo": [],
       "big": {
         "cylinder": [],
         "cone": [],
         "cat": [],
         "dog": []
       },
       "small": []
     },
     "goo": [],
     "yeah": []
   }
 ]

我有一个可以合并所有OBJECT值的查询，但是由于存在非OBJECT值，查询失败

select cast(
  concat('{'
    group_concat(
      substring(arbitraryjsonvalues, 2, length(arbitraryjsonvalues) - 2)
    ),
  '}')
as json) arbitraryjsonvaluesmerged
from ITEMS
where arbitraryjsonvalues != JSON_OBJECT();

在试图有一些接近我形成了这个逻辑，这可能看起来是错误的，所以我一直在做研究如何使用MySQL CASE与group_concat，和其他东西，看看我的查询在哪里不工作或如何使它工作，但还没有解决方案...
我谦卑地想表明我的错误尝试，希望有人能给我指出正确的方向。

select cast(
  concat('['
    (
      group_concat(
        CASE 
          WHEN JSON_TYPE(arbitraryjsonvalues) = "OBJECT" THEN 
            substring(
              arbitraryjsonvalues, 2, length(arbitraryjsonvalues) - 2
            )
          ELSE arbitraryjsonvalues
        END
      )
    ),
  ']')
as json) arbitraryjsonvaluesmerged
from ITEMS
where arbitraryjsonvalues != JSON_OBJECT() AND arbitraryjsonvalues != JSON_ARRAY() AND arbitraryjsonvalues != '' 
AND arbitraryjsonvalues != NULL;

我当前正尝试仅将OBJECT类型与下面的查询合并，但出现以下错误

Invalid JSON text in argument 2 to function json_merge_preserve: "Invalid value." at position 0.

set @t = @@group_concat_max_len;
set @@group_concat_max_len = 4294967295;
SELECT (
  select coalesce(
    json_merge_preserve(
      '{}', 
      GROUP_CONCAT(
        CONCAT(
          "'",
          (
            CASE
              WHEN JSON_TYPE(arbitraryjsonvalues) = "OBJECT"
              THEN arbitraryjsonvalues
            END
          ),
          "'"
        )
      )
    ),
    JSON_OBJECT()
  ) FROM ITEMS
);
set @@group_concat_max_len = @t;