explode_json_array

description

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

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

syntax

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

example

原表数据:

  1. mysql> select k1, e1 from example1 lateral view explode_json_array_int('[]') tmp1 as e1 order by k1, e1;
  2. +------+------+
  3. | k1 | e1 |
  4. +------+------+
  5. | 1 | NULL |
  6. | 2 | NULL |
  7. | 3 | NULL |
  8. +------+------+
  9. mysql> select k1, e1 from example1 lateral view explode_json_array_int('[1,2,3]') tmp1 as e1 order by k1, e1;
  10. +------+------+
  11. | k1 | e1 |
  12. +------+------+
  13. | 1 | 1 |
  14. | 1 | 2 |
  15. | 1 | 3 |
  16. | 2 | 1 |
  17. | 2 | 2 |
  18. | 2 | 3 |
  19. | 3 | 1 |
  20. | 3 | 2 |
  21. | 3 | 3 |
  22. +------+------+
  23. mysql> select k1, e1 from example1 lateral view explode_json_array_int('[1,"b",3]') tmp1 as e1 order by k1, e1;
  24. +------+------+
  25. | k1 | e1 |
  26. +------+------+
  27. | 1 | NULL |
  28. | 1 | 1 |
  29. | 1 | 3 |
  30. | 2 | NULL |
  31. | 2 | 1 |
  32. | 2 | 3 |
  33. | 3 | NULL |
  34. | 3 | 1 |
  35. | 3 | 3 |
  36. +------+------+
  37. mysql> select k1, e1 from example1 lateral view explode_json_array_int('["a","b","c"]') tmp1 as e1 order by k1, e1;
  38. +------+------+
  39. | k1 | e1 |
  40. +------+------+
  41. | 1 | NULL |
  42. | 1 | NULL |
  43. | 1 | NULL |
  44. | 2 | NULL |
  45. | 2 | NULL |
  46. | 2 | NULL |
  47. | 3 | NULL |
  48. | 3 | NULL |
  49. | 3 | NULL |
  50. +------+------+
  51. mysql> select k1, e1 from example1 lateral view explode_json_array_int('{"a": 3}') tmp1 as e1 order by k1, e1;
  52. +------+------+
  53. | k1 | e1 |
  54. +------+------+
  55. | 1 | NULL |
  56. | 2 | NULL |
  57. | 3 | NULL |
  58. +------+------+
  59. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[]') tmp1 as e1 order by k1, e1;
  60. +------+------+
  61. | k1 | e1 |
  62. +------+------+
  63. | 1 | NULL |
  64. | 2 | NULL |
  65. | 3 | NULL |
  66. +------+------+
  67. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[1,2,3]') tmp1 as e1 order by k1, e1;
  68. +------+------+
  69. | k1 | e1 |
  70. +------+------+
  71. | 1 | NULL |
  72. | 1 | NULL |
  73. | 1 | NULL |
  74. | 2 | NULL |
  75. | 2 | NULL |
  76. | 2 | NULL |
  77. | 3 | NULL |
  78. | 3 | NULL |
  79. | 3 | NULL |
  80. +------+------+
  81. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[1,"b",3]') tmp1 as e1 order by k1, e1;
  82. +------+------+
  83. | k1 | e1 |
  84. +------+------+
  85. | 1 | NULL |
  86. | 1 | NULL |
  87. | 1 | NULL |
  88. | 2 | NULL |
  89. | 2 | NULL |
  90. | 2 | NULL |
  91. | 3 | NULL |
  92. | 3 | NULL |
  93. | 3 | NULL |
  94. +------+------+
  95. 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;
  96. +------+------+
  97. | k1 | e1 |
  98. +------+------+
  99. | 1 | 1 |
  100. | 1 | 2 |
  101. | 1 | 3 |
  102. | 2 | 1 |
  103. | 2 | 2 |
  104. | 2 | 3 |
  105. | 3 | 1 |
  106. | 3 | 2 |
  107. | 3 | 3 |
  108. +------+------+
  109. mysql> select k1, e1 from example1 lateral view explode_json_array_double('[1,"b",3]') tmp1 as e1 order by k1, e1;
  110. +------+------+
  111. | k1 | e1 |
  112. +------+------+
  113. | 1 | NULL |
  114. | 1 | NULL |
  115. | 1 | NULL |
  116. | 2 | NULL |
  117. | 2 | NULL |
  118. | 2 | NULL |
  119. | 3 | NULL |
  120. | 3 | NULL |
  121. | 3 | NULL |
  122. +------+------+
  123. mysql> select k1, e1 from example1 lateral view explode_json_array_double('["a","b","c"]') tmp1 as e1 order by k1, e1;
  124. +------+------+
  125. | k1 | e1 |
  126. +------+------+
  127. | 1 | NULL |
  128. | 1 | NULL |
  129. | 1 | NULL |
  130. | 2 | NULL |
  131. | 2 | NULL |
  132. | 2 | NULL |
  133. | 3 | NULL |
  134. | 3 | NULL |
  135. | 3 | NULL |
  136. +------+------+
  137. mysql> select k1, e1 from example1 lateral view explode_json_array_double('{"a": 3}') tmp1 as e1 order by k1, e1;
  138. +------+------+
  139. | k1 | e1 |
  140. +------+------+
  141. | 1 | NULL |
  142. | 2 | NULL |
  143. | 3 | NULL |
  144. +------+------+
  145. mysql> select k1, e1 from example1 lateral view explode_json_array_string('[]') tmp1 as e1 order by k1, e1;
  146. +------+------+
  147. | k1 | e1 |
  148. +------+------+
  149. | 1 | NULL |
  150. | 2 | NULL |
  151. | 3 | NULL |
  152. +------+------+
  153. 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;
  154. +------+----------+
  155. | k1 | e1 |
  156. +------+----------+
  157. | 1 | 1.000000 |
  158. | 1 | 2.000000 |
  159. | 1 | 3.000000 |
  160. | 2 | 1.000000 |
  161. | 2 | 2.000000 |
  162. | 2 | 3.000000 |
  163. | 3 | 1.000000 |
  164. | 3 | 2.000000 |
  165. | 3 | 3.000000 |
  166. +------+----------+
  167. mysql> select k1, e1 from example1 lateral view explode_json_array_string('[1,"b",3]') tmp1 as e1 order by k1, e1;
  168. +------+------+
  169. | k1 | e1 |
  170. +------+------+
  171. | 1 | 1 |
  172. | 1 | 3 |
  173. | 1 | b |
  174. | 2 | 1 |
  175. | 2 | 3 |
  176. | 2 | b |
  177. | 3 | 1 |
  178. | 3 | 3 |
  179. | 3 | b |
  180. +------+------+
  181. mysql> select k1, e1 from example1 lateral view explode_json_array_string('["a","b","c"]') tmp1 as e1 order by k1, e1;
  182. +------+------+
  183. | k1 | e1 |
  184. +------+------+
  185. | 1 | a |
  186. | 1 | b |
  187. | 1 | c |
  188. | 2 | a |
  189. | 2 | b |
  190. | 2 | c |
  191. | 3 | a |
  192. | 3 | b |
  193. | 3 | c |
  194. +------+------+
  195. mysql> select k1, e1 from example1 lateral view explode_json_array_string('{"a": 3}') tmp1 as e1 order by k1, e1;
  196. +------+------+
  197. | k1 | e1 |
  198. +------+------+
  199. | 1 | NULL |
  200. | 2 | NULL |
  201. | 3 | NULL |
  202. +------+------+
  203. mysql> select k1, e1 from example1 lateral view explode_json_array_json('[{"id":1,"name":"John"},{"id":2,"name":"Mary"},{"id":3,"name":"Bob"}]') tmp1 as e1 order by k1, e1;
  204. +------+------------------------+
  205. | k1 | e1 |
  206. +------+------------------------+
  207. | 1 | {"id":1,"name":"John"} |
  208. | 1 | {"id":2,"name":"Mary"} |
  209. | 1 | {"id":3,"name":"Bob"} |
  210. | 2 | {"id":1,"name":"John"} |
  211. | 2 | {"id":2,"name":"Mary"} |
  212. | 2 | {"id":3,"name":"Bob"} |
  213. | 3 | {"id":1,"name":"John"} |
  214. | 3 | {"id":2,"name":"Mary"} |
  215. | 3 | {"id":3,"name":"Bob"} |
  216. +------+------------------------+

keywords

explode,json,array,json_array,explode_json,explode_json_array