explode_json_array

description

Table functions must be used in conjunction with Lateral View.

Expand a json array. According to the array element type, there are three function names. Corresponding to integer, floating point and string arrays respectively.

grammar:

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

example

Original table data:

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

keywords

explode,json,array,json_array,explode_json,explode_json_array