Mongo DB Array

Hello Helical,

I am using MongoDB via Drill on Helical Insight 4.0. I have an ID which is created by multiple creators. I would like to display all the creators for a single ID in a single row and separated by comma. For example, if ID = abq123 is created by user1, user2 and user3, I would like to show it as

ID Creators
abq123 user1, user2, user3

Is there any DBFunction that will help me achieve this?

Thank You,

Hello Sesa,

You can create a view where you can achieve the above style of data using SQL. Below is the SQL for your reference:

select a.dateCreated, a.RecipientName, a.ID, a.creators
(select `p`.`dateCreated`,
`p`.`ip`.`recipient`.`name` as RecipientName,
`p`.`ip`.`recipient`.`recipientCode` as RecipientCode,
`p`.`ip`.`title` as IPTitle,
`p`.`group` as IPGroup,
`p`.`ip`.`dateOfDisclosure` as iP7DateOfDisclosure,
`p`.`ip`.`stageOfDevelopment` as iP7stageOfDevelopment,
flatten(`p`.`ip`.`creators`) as creators,
`p`.`_id`.`$oid` as `ID`
from mongo.nipmo.iP7 p
limit 100) a

Thank You
From Team Helical.