mysql 数组 json字符串转数组,将MySQL中的JSON数组转换为行

UPDATE: This is now possible in MySQL 8 via the JSON_TABLE function: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions.html

I'm loving the new JSON functions in MySQL 5.7, but running into a block trying to merge values from JSON into a normal table structure.

Grabbing JSON, manipulating and extracting arrays from it etc. is simple. JSON_EXTRACT all the way. But what about the inverse, going from a JSON array to rows? Perhaps I am dense on the existing MySQL JSON functionality, but I haven't been able to figure that one out.

For example, say I have a JSON array and want to insert a row for each element in the array with its value? The only way I have found is to write a bunch of JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') etc and union them together.

Or, say I have a JSON array and want to GROUP_CONCAT() it to a single comma separated string?

In other words, I know I can do this:

SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val

FROM

(

SELECT 0 AS n

UNION

SELECT 1 AS n

UNION

SELECT 2 AS n

UNION

SELECT 3 AS n

UNION

SELECT 4 AS n

UNION

SELECT 5 AS n

) x

WHERE x.n < JSON_LENGTH(@j);

But that hurts my eyes. And my heart.

How can I do something like:

SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))

... and have it concatenate together the values in the array vs. the JSON array itself?

I guess what I'm looking for here is some sort of JSON_SPLIT along the lines of:

SET @j = '[1, 2, 3]';

SELECT GROUP_CONCAT(val)

FROM

JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')

If MySQL had a proper STRING_SPLIT(val, 'separator') table returning function, I could hack it (escaping be damned), but that's not available either.

解决方案

Here's how to do this with JSON_TABLE in MySQL 8+:

SELECT *

FROM

JSON_TABLE(

'[5, 6, 7]',

"$[*]"

COLUMNS(

Value INT PATH "$"

)

) data;

You can also use this as a general string split function which MySQL otherwise lacks (similar to PG's regexp_split_to_table or MSSQL's STRING_SPLIT) by taking a delimited string and turning it into a JSON string:

set @delimited = 'a,b,c';

SELECT *

FROM

JSON_TABLE(

CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),

"$[*]"

COLUMNS(

Value varchar(50) PATH "$"

)

) data;

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值