sql mysql 8.x系のlateral句やjson関数に触ってみた

まえがき

lateral句が実装されたとのことで、触れてみた。json関数にも触れてみた。

スクリプト

split系の関数が欲しい。。。

[root@7b214415ce2e /]# cat ./a.sh
# !/bin/bash

f(){
  start_rn=$1;shift;
  end_rn=$1;shift;
  mysql -uroot -pMysql3306 -t -n < <(cat <<EOS

	set @nat=${start_rn}-1;

	with nats as(
	  select @nat := @nat + 1 as n from information_schema.columns limit ${end_rn}
	) select s1.n,s2.n from nats s1,lateral(select s2.n from nats s2 where s1.n<=s2.n) s2;

	select version();
EOS
) 2>/dev/null
}

f "$@"

いけてる。

[root@7b214415ce2e /]# ./a.sh 1 5
+------+------+
| n    | n    |
+------+------+
|    1 |    1 |
|    1 |    2 |
|    1 |    3 |
|    1 |    4 |
|    1 |    5 |
|    2 |    2 |
|    2 |    3 |
|    2 |    4 |
|    2 |    5 |
|    3 |    3 |
|    3 |    4 |
|    3 |    5 |
|    4 |    4 |
|    4 |    5 |
|    5 |    5 |
+------+------+
+-----------+
| version() |
+-----------+
| 8.0.18    |
+-----------+

行展開機能があるjson_table関数も触れてみた。

with sub as(
    select '[{"x":"8"},{"x":"3"},{"x":"4"}]' as rsv_args
)
select s1.rsv_args,s2.ele
from
  sub s1,json_table(
    rsv_args,
    "$[*]" columns(
      ele longtext path "$.x" 
    )
  ) as s2;

jqコマンドと仲良くしてく必要があるのかなー。XXX_table関数少ない。。

[root@7b214415ce2e /]# mysql -uroot -pMysql3306
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 102
Server version: 8.0.18 MySQL Community Server - GPL

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> with sub as(
    ->     select '[{"x":"8"},{"x":"3"},{"x":"4"}]' as rsv_args
    -> )
    -> select s1.rsv_args,s2.ele
    -> from
    ->   sub s1,json_table(
    ->     rsv_args,
    ->     "$[*]" columns(
    ->       ele longtext path "$.x" 
    ->     )
    ->   ) as s2;
+---------------------------------+------+
| rsv_args                        | ele  |
+---------------------------------+------+
| [{"x":"8"},{"x":"3"},{"x":"4"}] | 8    |
| [{"x":"8"},{"x":"3"},{"x":"4"}] | 3    |
| [{"x":"8"},{"x":"3"},{"x":"4"}] | 4    |
+---------------------------------+------+
3 rows in set (0.00 sec)

json系で面白そうなのも触れてみた。json-arrayagg。json-objectagg。分析関数としても使える。

with sub as(
    select '[{"x":"8"},{"x":"3"},{"x":"4"}]' as rsv_args
)
select s1.rsv_args,s2.ele
,concat("key",cast(row_number()over(order by s2.ele) as char)) as new_key
,json_objectagg("xxx",s2.ele) over(order by s2.ele) as non_ary_jobj
,json_arrayagg(s2.ele) over(order by s2.ele) as ary_jobj
,json_arrayagg(s2.ele) over() as mx_ary_jobj
from
  sub s1,json_table(
    rsv_args,
    "$[*]" columns(
      ele longtext path "$.x" 
    )
  ) as s2;

コレクション無いから、jsonリストで持ち回るのかな。json_objectagg関数は階段にならないなー。

mysql> with sub as(
    ->     select '[{"x":"8"},{"x":"3"},{"x":"4"}]' as rsv_args
    -> )
    -> select s1.rsv_args,s2.ele
    -> ,concat("key",cast(row_number()over(order by s2.ele) as char)) as new_key
    -> ,json_objectagg("xxx",s2.ele) over(order by s2.ele) as non_ary_jobj
    -> ,json_arrayagg(s2.ele) over(order by s2.ele) as ary_jobj
    -> ,json_arrayagg(s2.ele) over() as mx_ary_jobj
    -> from
    ->   sub s1,json_table(
    ->     rsv_args,
    ->     "$[*]" columns(
    ->       ele longtext path "$.x" 
    ->     )
    ->   ) as s2;
+---------------------------------+------+---------+--------------+-----------------+-----------------+
| rsv_args                        | ele  | new_key | non_ary_jobj | ary_jobj        | mx_ary_jobj     |
+---------------------------------+------+---------+--------------+-----------------+-----------------+
| [{"x":"8"},{"x":"3"},{"x":"4"}] | 3    | key1    | {"xxx": "3"} | ["3"]           | ["3", "4", "8"] |
| [{"x":"8"},{"x":"3"},{"x":"4"}] | 4    | key2    | {"xxx": "4"} | ["3", "4"]      | ["3", "4", "8"] |
| [{"x":"8"},{"x":"3"},{"x":"4"}] | 8    | key3    | {"xxx": "8"} | ["3", "4", "8"] | ["3", "4", "8"] |
+---------------------------------+------+---------+--------------+-----------------+-----------------+
3 rows in set (0.00 sec)

あとがき

json関数で再帰処理して回転コマンド作ってみる。割とこの環境構築するのに苦労した。。。

以上、ありがとうございました。