将MySQL中的JSON数组转换为行 [英] Convert JSON array in MySQL to rows

问题描述

更新:现在可以在MySQL 8中通过JSON_TABLE函数进行操作:

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

我喜欢MySQL 5.7中的新JSON函数，但是遇到了一个试图将JSON中的值合并到普通表结构中的块.

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.

获取JSON，从中处理和提取数组等非常简单.始终使用JSON_EXTRACT.但是从JSON数组到行的逆过程呢?也许我对现有的MySQL JSON功能非常关注，但是我无法弄清楚这一点.

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.

例如，假设我有一个JSON数组，并想为数组中的每个元素插入一行及其值?我发现的唯一方法是编写一堆JSON_EXTRACT(...'$ [0]')JSON_EXTRACT(...'$ [1]')等并将它们结合在一起.

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.

或者说我有一个JSON数组，并想将它GROUP_CONCAT()成为一个逗号分隔的字符串?

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.

我该如何做:

SET @j = '[1, 2, 3]';
SELECT GROUP_CONCAT(JSON_EXTRACT(@j, '$[ * ]'))

...并将数组中的值与JSON数组本身连接在一起?

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

我想我在这里寻找的是某种类似于JSON_SPLIT的代码:

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, '$[ * ]'), '$')

如果MySQL具有适当的STRING_SPLIT(val，'separator')表返回函数，我可以破解它(逃避该死)，但这也不可用.

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;

您还可以通过将带分隔符的字符串并将其转换为JSON字符串，将其用作MySQL否则缺少的常规字符串拆分函数(类似于PG的regexp_split_to_table或MSSQL的STRING_SPLIT):

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;

这篇关于将MySQL中的JSON数组转换为行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！