Need help with N1QL

SQL++
1

I have the below SQL ++ query

select  t1.admins_devs
from 
( 
select ARRAY_FLATTEN(ARRAY_AGG( DISTINCT data.additionalAdmins),1) as admins_devs  from api_external where type = "partnerApp" 
and data.companyId = "5b0405112baf4695b2ed994d43402b70" 
and deleted = false and backupOf is null and data.additionalAdmins is not null
union
select ARRAY_FLATTEN(ARRAY_AGG( DISTINCT data.additionalDevelopers),1) as admins_devs  from api_external where type = "partnerApp" 
and data.companyId = "5b0405112baf4695b2ed994d43402b70" 
and deleted = false and backupOf is null and data.additionalDevelopers is not null
)t1

It gives result as below.

[
  {
    "admins_devs": [
      "3242eb1964334ce2861ee10e27473705",
      "a99a6cfd45c4438d95080a09856b8075"
    ]
  },
  {
    "admins_devs": [
      "6c5281d6f6d846d2bd09853ff5daea0d",
      "3242eb1964334ce2861ee10e27473705"
    ]
  }
]

I want a single array with all the values.

[
      "3242eb1964334ce2861ee10e27473705",
      "a99a6cfd45c4438d95080a09856b8075",
      "6c5281d6f6d846d2bd09853ff5daea0d"
]

Please note that any of the 2 sql ++ being union can return null . We want to be able to handle that. There maight be results that are common between upper and lower SQL ++ being union , result must have only 1 instance of all occurrences

2

You haven’t stated what you’ve attempted?

You want a single string (or possibly null) value for each result not a single result that is an unnamed array of strings (and possibly a null) ?

For a single result that is an array of strings, try changing:

To:

select raw array_distinct(array_flatten(array_agg(t1.admins_devs),1))

instead. You don’t state what you want to happen with NULL results - if you don’t want them, then add WHERE t1.admins_dev IS NOT NULL to the outer select. If you want a null value in the final array, you don’t need to do anything (a single null will be in the final array).

If you want single-level values as the results, one way is to UNNEST (LEFT if you want to include a null) your results and simply SELECT DISTINCT:

select distinct raw ad
from
(
      select ARRAY_FLATTEN(ARRAY_AGG( DISTINCT data.additionalAdmins),1) as admins_devs
      from api_external
      where type = "partnerApp" 
      and data.companyId = "5b0405112baf4695b2ed994d43402b70" 
      and deleted = false
      and backupOf is null
      and data.additionalAdmins is not null
    union
      select ARRAY_FLATTEN(ARRAY_AGG( DISTINCT data.additionalDevelopers),1) as admins_devs
      from api_external
      where type = "partnerApp" 
      and data.companyId = "5b0405112baf4695b2ed994d43402b70" 
      and deleted = false
      and backupOf is null
      and data.additionalDevelopers is not null
) t1
left unnest admins_devs ad

HTH.