Tulis semula tajuk sebagai: Tukar tatasusunan JSON kepada baris dalam MySQL

Question

Kemas kini: Ini kini boleh dilakukan dalam MySQL 8 melalui fungsi JSON_TABLE: https://dev.mysql.com/doc/refman/8.0/en/json-table-functions .html<

Saya suka fungsi JSON baharu dalam MySQL 5.7, tetapi saya menghadapi masalah cuba menggabungkan nilai daripada JSON ke dalam struktur jadual biasa.

Mudah untuk mendapatkan JSON, memanipulasinya, mengekstrak tatasusunan daripadanya dan banyak lagi. JSON_EXTRACT sepanjang jalan. Tetapi bagaimana pula sebaliknya, dari tatasusunan JSON ke baris? Mungkin saya berminat dengan fungsi MySQL JSON yang sedia ada, tetapi saya tidak dapat memikirkannya.

Sebagai contoh, katakan saya mempunyai tatasusunan JSON dan ingin memasukkan baris untuk setiap elemen dalam tatasusunan dan nilainya? Satu-satunya cara yang saya temui ialah menulis sekumpulan JSON_EXTRACT(... '$[0]') JSON_EXTRACT(... '$[1]') dan lain-lain dan gabungkannya bersama-sama.

Sebagai alternatif, katakan saya mempunyai tatasusunan JSON dan mahu GROUP_CONCAT() menjadi rentetan yang dipisahkan koma?

Dalam erti kata lain, saya tahu saya boleh melakukan ini:

SET @j = '[1, 2, 3]';
PILIH GROUP_CONCAT(JSON_EXTRACT(@j, CONCAT('$[', x.n, ']'))) AS val
  DARI
  (
    PILIH 0 SEBAGAI n
    KESATUAN
    PILIH 1 SEBAGAI n
    KESATUAN
    PILIH 2 SEBAGAI n
    KESATUAN
    PILIH 3 SEBAGAI n
    KESATUAN
    PILIH 4 SEBAGAI n
    KESATUAN
    PILIH 5 SEBAGAI n
  ) x
DI MANA x.n < JSON_LENGTH(@j);

Tetapi ia menyakitkan mata saya. Dan hati saya.

Bagaimana saya boleh melakukan sesuatu seperti ini:

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

...dan gabungkan nilai dalam tatasusunan dengan tatasusunan JSON itu sendiri?

Saya rasa apa yang saya cari di sini ialah sejenis JSON_SPLIT , seperti:

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

PILIH GROUP_CONCAT(val)
DARI
  JSON_SPLIT(JSON_EXTRACT(@j, '$[ * ]'), '$')

Jika MySQL mempunyai fungsi pemulangan jadual STRING_SPLIT(val, 'pemisah') yang betul, saya boleh menggodamnya (melarikan diri sialan), tetapi itu juga tidak berfungsi.

Answer 1

Memang menyahnormalkan kepada JSON bukanlah idea yang baik, tetapi kadangkala anda perlu berurusan dengan data JSON dan terdapat cara untuk mengekstrak tatasusunan JSON ke dalam baris dalam pertanyaan.

Caranya ialah dengan melakukan gabungan pada jadual indeks sementara atau sebaris, yang memberikan anda baris untuk setiap nilai bukan nol dalam tatasusunan JSON. Iaitu, jika anda mempunyai jadual dengan nilai 0, 1 dan 2 dan anda menyertainya ke tatasusunan JSON "ikan" yang mengandungi dua entri, maka Fish[0] sepadan dengan 0, dengan itu menghasilkan satu baris, dan Fish 1 sepadan dengan 1, menghasilkan baris kedua, tetapi fish[2] adalah batal, jadi ia tidak sepadan dengan 2 dan tiada baris dihasilkan dalam cantuman. Anda memerlukan seberapa banyak nombor dalam jadual indeks sebagai panjang maksimum mana-mana tatasusunan dalam data JSON. Ini adalah sedikit hack, dan menyakitkan seperti contoh OP, tetapi ia sangat mudah.

Contoh (memerlukan MySQL 5.7.8 atau lebih tinggi):

CREATE TABLE t1 (rec_num INT, jdoc JSON);
INSERT INTO t1 VALUES 
  (1, '{"fish": ["red", "blue"]}'), 
  (2, '{"fish": ["one", "two", "three"]}');

SELECT
  rec_num,
  idx,
  JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) AS fishes
FROM t1
  -- Inline table of sequential values to index into JSON array
JOIN ( 
  SELECT  0 AS idx UNION
  SELECT  1 AS idx UNION
  SELECT  2 AS idx UNION
  -- ... continue as needed to max length of JSON array
  SELECT  3
  ) AS indexes
WHERE JSON_EXTRACT(jdoc, CONCAT('$.fish[', idx, ']')) IS NOT NULL
ORDER BY rec_num, idx;

Hasilnya ialah:

+---------+-----+---------+
| rec_num | idx | fishes  |
+---------+-----+---------+
|       1 |   0 | "red"   |
|       1 |   1 | "blue"  |
|       2 |   0 | "one"   |
|       2 |   1 | "two"   |
|       2 |   2 | "three" |
+---------+-----+---------+

Nampaknya pasukan MySQL mungkin menambah fungsi JSON_TABLE dalam MySQL 8 untuk memudahkan perkara ini. (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/JSON_TABLE 函数，以使这一切变得更容易。 (http://mysqlserverteam.com/mysql-8-0-labs -json-aggregation-functions/) （MySQL 团队已经添加了JSON_TABLE) (Pasukan MySQL telah menambah JSON_TABLE fungsi.)

Answer 2

Berikut ialah cara menggunakan JSON_TABLE dalam MySQL 8+:

SELECT *
     FROM
       JSON_TABLE(
         '[5, 6, 7]',
         "$[*]"
         COLUMNS(
           Value INT PATH "$"
         )
       ) data;

Anda juga boleh menggunakannya sebagai fungsi pemisahan rentetan tujuan umum yang kekurangan MySQL (serupa dengan regexp_split_to_table PG atau STRING_SPLIT MSSQL) dengan mengambil rentetan yang dipisahkan dan menukarnya kepada rentetan JSON:

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

SELECT *
     FROM
       JSON_TABLE(
         CONCAT('["', REPLACE(@delimited, ',', '", "'), '"]'),
         "$[*]"
         COLUMNS(
           Value varchar(50) PATH "$"
         )
       ) data;