explode_json_array

description

表函数，需配合 Lateral View 使用。

展开一个 json 数组。根据数组元素类型，有三种函数名称。分别对应整型、浮点和字符串数组。

语法：

1. explode_json_array_int(json_str)
2. explode_json_array_double(json_str)
3. explode_json_array_string(json_str)

example

原表数据：

1. mysql> select k1 from example1 order by k1;
2. +------+
3. | k1   |
4. +------+
5. |    1 |
6. |    2 |
7. |    3 |
8. |    4 |
9. |    5 |
10. |    6 |
11. +------+

Lateral View:

1. mysql> select k1, e1 from example1 lateral view explode_json_array_int('[]') tmp1 as e1 order by k1, e1;
2. Empty set

4. mysql> select k1, e1 from example1 lateral view explode_json_array_int('[1,2,3]') tmp1 as e1 order by k1, e1;
5. +------+------+
6. | k1   | e1   |
7. +------+------+
8. |    1 |    1 |
9. |    1 |    2 |
10. |    1 |    3 |
11. |    2 |    1 |
12. |    2 |    2 |
13. |    2 |    3 |
14. |    3 |    1 |
15. |    3 |    2 |
16. |    3 |    3 |
17. +------+------+

19. mysql> select k1, e1 from example1 lateral view explode_json_array_int('[1,"b",3]') tmp1 as e1 order by k1, e1;
20. +------+------+
21. | k1   | e1   |
22. +------+------+
23. |    1 |    1 |
24. |    1 |    3 |
25. |    2 |    1 |
26. |    2 |    3 |
27. |    3 |    1 |
28. |    3 |    3 |
29. +------+------+

31. mysql> select k1, e1 from example1 lateral view explode_json_array_int('["a","b","c"]') tmp1 as e1 order by k1, e1;
32. Empty set

34. mysql> select k1, e1 from example1 lateral view explode_json_array_int('{"a": 3}') tmp1 as e1 order by k1, e1;
35. Empty set

37. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[]') tmp1 as e1 order by k1, e1;
38. Empty set

40. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[1,2,3]') tmp1 as e1 order by k1, e1;
41. Empty set

43. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[1,"b",3]') tmp1 as e1 order by k1, e1;
44. Empty set

46. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[1.0,2.0,3.0]') tmp1 as e1 order by k1, e1;
47. +------+------+
48. | k1   | e1   |
49. +------+------+
50. |    1 |    1 |
51. |    1 |    2 |
52. |    1 |    3 |
53. |    2 |    1 |
54. |    2 |    2 |
55. |    2 |    3 |
56. |    3 |    1 |
57. |    3 |    2 |
58. |    3 |    3 |
59. +------+------+

61. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[1,"b",3]') tmp1 as e1 order by k1, e1;
62. Empty set

64. mysql> select k1, e1 from example1 lateral view explode_json_array_double('["a","b","c"]') tmp1 as e1 order by k1, e1;
65. Empty set

67. mysql> select k1, e1 from example1 lateral view explode_json_array_double('{"a": 3}') tmp1 as e1 order by k1, e1;
68. Empty set

70. mysql> select k1, e1 from example1 lateral view explode_json_array_string('[]') tmp1 as e1 order by k1, e1;
71. Empty set

73. mysql> select k1, e1 from example1 lateral view explode_json_array_string('[1.0,2.0,3.0]') tmp1 as e1 order by k1, e1;
74. +------+----------+
75. | k1   | e1       |
76. +------+----------+
77. |    1 | 1.000000 |
78. |    1 | 2.000000 |
79. |    1 | 3.000000 |
80. |    2 | 1.000000 |
81. |    2 | 2.000000 |
82. |    2 | 3.000000 |
83. |    3 | 1.000000 |
84. |    3 | 2.000000 |
85. |    3 | 3.000000 |
86. +------+----------+

88. mysql> select k1, e1 from example1 lateral view explode_json_array_string('[1,"b",3]') tmp1 as e1 order by k1, e1;
89. +------+------+
90. | k1   | e1   |
91. +------+------+
92. |    1 | 1    |
93. |    1 | 3    |
94. |    1 | b    |
95. |    2 | 1    |
96. |    2 | 3    |
97. |    2 | b    |
98. |    3 | 1    |
99. |    3 | 3    |
100. |    3 | b    |
101. +------+------+

103. mysql> select k1, e1 from example1 lateral view explode_json_array_string('["a","b","c"]') tmp1 as e1 order by k1, e1;
104. +------+------+
105. | k1   | e1   |
106. +------+------+
107. |    1 | a    |
108. |    1 | b    |
109. |    1 | c    |
110. |    2 | a    |
111. |    2 | b    |
112. |    2 | c    |
113. |    3 | a    |
114. |    3 | b    |
115. |    3 | c    |
116. +------+------+

118. mysql> select k1, e1 from example1 lateral view explode_json_array_string('{"a": 3}') tmp1 as e1 order by k1, e1;
119. Empty set

keywords

1. explode_json_array