Unnest column of arrays

InfluxDB 2
, , ,
1

Hi,
I have a column of arrays like this:

| time---------------------------------| value
0 2022-09-16 00:02:01+00:00 null
1 2022-09-16 00:28:38+00:00 [‘A’, ‘B’, ‘C’, ‘D’]
2 2022-09-16 00:30:33+00:00 null
3 2022-09-16 00:35:21+00:00 [‘E’, ‘F’, ‘G’]
4 2022-09-16 00:43:17+00:00 [‘H’]

What is the suitable query to unnest arrays and convert each item to single row like this:

| time---------------------------------| value
0 2022-09-16 00:02:01+00:00 null
1 2022-09-16 00:28:38+00:00 ‘A’
2 2022-09-16 00:28:38+00:00 ‘B’
3 2022-09-16 00:28:38+00:00 ‘C’
4 2022-09-16 00:28:38+00:00 ‘D’
5 2022-09-16 00:30:33+00:00 null
6 2022-09-16 00:35:21+00:00 ‘E’
7 2022-09-16 00:35:21+00:00 ‘F’
8 2022-09-16 00:35:21+00:00 ‘G’
9 2022-09-16 00:43:17+00:00 ‘H’

2

Hello @Amir_Ayat,
You’ll want to use the array.map function.

But just to clarify is each array in a different table as a part of the same stream? what is the index referring to?
Also how are you writing data to InfluxDB?
Are those arrays converted to strings? InfluxDB doesnt support arrays as a record type.
Thank you!

3

Hi,

Thank you for your response,

In my project, some posts are being gathered from social media every hour. Post data are analyzed by NLP agent and tagged by different sentiment categories. So each post may have multiple sentiment categories which are represented in an array attached to the post. Finally the data is inserted to InfluxDB from Apache Kafka in a single table. I didn’t set any custom index and I didnt know InfluxDB does not support arrays as a record type. Maybe arrays are automatically converted to string when they are inserted by python driver.

I want to parse each array to separate items in order to count duplicates. I know it can be done by the “unwind” operator in MongoDB. “Unnest” can do the same action in RDBMS like Postgresql.

I would appreciate it if you could write a sample query of array.map().