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

explode,json,array,json_array,explode_json,explode_json_array