Table Functions - EXPLODE_JSON_ARRAY - 《Apache Doris v2.0 Documentation》

explode_json_array
- description
  - syntax
- example
- keywords

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.

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

Original table data:

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

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. +------+------+

220. 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;
221. +------+------------------------+
222. | k1   | e1                     |
223. +------+------------------------+
224. |    1 | {"id":1,"name":"John"} |
225. |    1 | {"id":2,"name":"Mary"} |
226. |    1 | {"id":3,"name":"Bob"}  |
227. |    2 | {"id":1,"name":"John"} |
228. |    2 | {"id":2,"name":"Mary"} |
229. |    2 | {"id":3,"name":"Bob"}  |
230. |    3 | {"id":1,"name":"John"} |
231. |    3 | {"id":2,"name":"Mary"} |
232. |    3 | {"id":3,"name":"Bob"}  |
233. +------+------------------------+

keywords

explode,json,array,json_array,explode_json,explode_json_array