MySQL中有两个JSON数组字段,如下所示:
["a", "b", "c"]
["apple", "banana", "coconut"]
现在我想将它们组合成一个JSON对象,如下所示:
{"a":"apple", "b":"banana", "c":"coconut"}
有MySQL函数吗?
我会用一种简单的方式来处理这个问题。
- 使用
JSON_TABLE()
取消对两个JSON结构的测试 - 把两张桌子连在一起
- 构造适当的JSON对象并进行聚合
以下实现了此逻辑。第一个CTE提取密钥。第二个提取值,最后将这些值组合起来:
WITH the_keys as (
SELECT j.*
FROM t CROSS JOIN
JSON_TABLE(t.jsdata1,
'$[*]'
columns (seqnum for ordinality, the_key varchar(255) path '$')
) j
),
the_values as (
SELECT j.*
FROM t CROSS JOIN
JSON_TABLE(t.jsdata2,
'$[*]'
columns (seqnum for ordinality, val varchar(255) path '$')
) j
)
select json_objectagg(the_keys.the_key, the_values.val)
from the_keys join
the_values
on the_keys.seqnum = the_values.seqnum;
这里有一个db<gt;不停摆弄
请注意,这是非常通用的(您可以向行中添加更多元素(。如果您在不同的行上有键/值对,并且它不使用不推荐使用的功能,那么您可以很容易地调整它以返回多行数据。
由于数组中每个元素的索引以及行生成的贡献,您可以通过JSON_EXTRACT()
函数从information_schema
中使用表进行提取,然后使用从子查询(如(返回的JSON_OBJECTAGG()
聚合所有结果
SELECT JSON_OBJECTAGG(Js1,Js2)
FROM
(
SELECT JSON_UNQUOTE(JSON_EXTRACT(jsdata1,CONCAT('$[',@rn+1,']'))) AS Js1,
JSON_UNQUOTE(JSON_EXTRACT(jsdata2,CONCAT('$[',@rn+1,']'))) AS Js2,
@rn := @rn + 1 AS rn
FROM tab AS t1
JOIN (SELECT @rn:=-1) AS r
JOIN information_schema.tables AS t2
-- WHERE @rn < JSON_LENGTH(jsdata1) - 1 #redundant for MariaDB, but needed for MySQL
) AS j
其中
假设'["a", "b", "c"]'
是列jsdata1
和的值
假定'["apple", "banana", "coconut"]'
是列jsdata2
的值
在仅包含插入的一行的表(tab
(内。
演示
使用JSON函数的基本方法,如:
select JSON_OBJECT(
JSON_UNQUOTE(JSON_EXTRACT(a, '$[0]')), JSON_EXTRACT(b, '$[0]'),
JSON_UNQUOTE(JSON_EXTRACT(a, '$[1]')), JSON_EXTRACT(b, '$[1]'),
JSON_UNQUOTE(JSON_EXTRACT(a, '$[2]')), JSON_EXTRACT(b, '$[2]')
) result from tbl;
SQL沙箱