This message was deleted.
# generals
Slackbot
09/30/2023, 5:34 AMThis message was deleted.
j
John Kowtko
09/30/2023, 4:40 PMHi Sadiq,
It's not straight forward, and I haven't explicitly tried this coming from JSON, but theoretically one of these two options might work:
• extract array element, cast to Array datatype, and UNNEST() it
• extract array element, case to MV datatype, and do a Group By on it which will automatically unnest it.
Let us know if you make any progress on this ... would be interested to hear.
Thanks. John
👍 1
s
Sadiq Khoja
09/30/2023, 4:41 PMThanks, let me try with
unnest()l
Laksh Singla
09/30/2023, 6:00 PMfyi, unnest won’t work with MSQ in the current Druid version (Druid 27).
its merged in master, in case you are trying it out on local setups, and it should mp be out in Druid 28.
👍 1
s
Sadiq Khoja
09/30/2023, 6:07 PMwhat’s my option now? I was hoping for some Field flattening specifications magic
j
John Kowtko
10/02/2023, 1:23 PMHi Sadiq,
Can you extract the array, then convert it to an MV, then unnest it using a Group By? The below SQL works for me ...
Copy code
select STRING_TO_MV(ARRAY_TO_STRING(array[1,2,3,4,5],','),',') key, channel, count(*)
from wikipedia where page like '%arrio%'
group by 1, 2s
Sadiq Khoja
10/03/2023, 3:15 AMThanks @John Kowtko,
I don’t think this will work in my case because my column is not just an array of string, it is a complex json
Sadiq Khoja
10/03/2023, 3:36 AMActually it works
Sadiq Khoja
10/03/2023, 3:36 AMlooks hacky but it works
Copy code
select JSON_QUERY(jsonObj, '$.name') name, JSON_QUERY(jsonObj, '$.chat.slack') slack
from (
select TRY_PARSE_JSON('{' || str || '}') jsonObj
from (
select STRING_TO_MV(TO_JSON_STRING("value"), '},\{') str from "Submissions"
group by 1
)
)j
John Kowtko
10/03/2023, 3:36 AMOkay, then in that case can you try using something like STRING_TO_MV(TO_JSON_STRING(JSON_QUERY(colName, '$.path_to_array_element')), ',') and pull it out as an MV?
s
Sadiq Khoja
10/03/2023, 3:36 AMI had to use
TO_JSON_STRINGSadiq Khoja
10/03/2023, 3:47 AMThanks @John Kowtko for the help
🙌 1