Please help me with the SQL++

SQL++
,
1

I have the following N1QL that gives the below results.

SELECT 
        ARRAY_AGG({"product":"product::"||t2.product})[*].product 
        
    FROM (SELECT t1.id,
        product,
        MIN(t1.env) AS env,
        MAX(status) AS status,
        MIN({t1.description,
        t1.dcrFlag,
        t1.lastModified,
        t1.name,
        t1.companyId}).*
    FROM (SELECT m.appName AS name,
        m.uuid AS id,
        m.description,
        m.env,
        m.companyId,
        m.fourthParty AS dcrFlag,
        coalesce(m.updatedTs,m.createdTs) AS lastModified,
        m.productStatus ,
        m.productId ,
        m.status
    FROM api_external AS pa
JOIN api_external AS i1
    ON pa.uuid = i1.data.partnerAppId
        AND i1.type = "integration"
    WHERE pa.type = "partnerApp"
        AND pa.data.companyId in [ "73f35ad2510a416f8e36b2e30c2230f1" ]
        AND pa.deleted = false
        AND i1.deleted = false
        AND i1.data.env in [ "SANDBOX","PRODUCTION" ]
        AND (IS_MISSING(i1.backupOf)
        OR IS_NULL (i1.backupOf)
        OR i1.backupOf="")
    GROUP BY  pa.uuid, i1.data.productStatus LETTING m = MIN([i1.data.env, {pa.uuid, pa.data.appName, pa.data.description, pa.data.fourthParty, i1.data.env, i1.data.productStatus , i1.createdTs,i1.updatedTs, i1.data.productId , i1.data.status,pa.data.companyId }])[1] ) AS t1 LEFT UNNEST OBJECT_PAIRS(t1.productStatus) AS ps LET product = IFMISSINGORNULL(t1.productId, ps.name), status = IFMISSINGORNULL(t1.status, ps.val)
    GROUP BY  t1.id, product) AS t2
    GROUP BY  t2.id

Result :

	
[
  {
    "product": [
      "product::0da7ebce7ed842ad8c28df4eed766808",
      "product::a68d99bbd45c490dbe006d1ce2831ca3",
      "product::c1584cf57553455c8a04d8cc984c7d88"
    ]
  },
  {
    "product": [
      "product::a68d99bbd45c490dbe006d1ce2831ca3"
    ]
  },
  {
    "product": [
      "product::faf8800c727f44478ed5a4bcde6a53e3"
    ]
  },
  {
    "product": [
      "product::4744165edc974dafa73a8895766140cb",
      "product::c1584cf57553455c8a04d8cc984c7d88"
    ]
  },
  {
    "product": [
      "product::0da7ebce7ed842ad8c28df4eed766808",
      "product::c1584cf57553455c8a04d8cc984c7d88"
    ]
  }
]

I want to get a single array with all the unique productIds.
Something like this

[
      "product::0da7ebce7ed842ad8c28df4eed766808",
      "product::a68d99bbd45c490dbe006d1ce2831ca3",
      "product::c1584cf57553455c8a04d8cc984c7d88",
      "product::faf8800c727f44478ed5a4bcde6a53e3
]
2

You can make the SELECT a SELECT RAW … and wrap it in an array_flatten() and array_distinct.

Something like:

SELECT
  array_distinct(
    array_flatten(
      (SELECT RAW ARRAY_AGG({"product":"product::"||t2.product})[*].product
        ...<rest of your statement> ...
      )
      ,1
    )
  )
;

Of course you likely could change the statements too, but as a simple quick “fix” the wrapping should work.

HTH.

3

You can perhaps try something like:

SELECT RAW ARRAY_DISTINCT(ARRAY_AGG("product::"||ps))
FROM 
(
    SELECT MIN([i1.data.env,{i1.data.productStatus,i1.data.productId}])[1].*
    FROM api_external AS pa
    JOIN api_external AS i1
        ON pa.uuid = i1.data.partnerAppId
        AND i1.type = "integration"
    WHERE pa.type = "partnerApp"
    AND pa.data.companyId in [ "73f35ad2510a416f8e36b2e30c2230f1" ]
    AND pa.deleted = false
    AND i1.deleted = false
    AND i1.data.env in [ "SANDBOX","PRODUCTION" ]
    AND NVL(i1.backupOf,"") = ""
    GROUP BY pa.uuid, i1.data.productStatus 
) AS t1
UNNEST NVL2(t1.productId,[t1.productId],OBJECT_NAMES(t1.productStatus)) AS ps

too.

Changes are basically:

  1. Don’t select/project fields you don’t use.
  2. Don’t group unnecessarily. This lets us remove a bounding select. Whilst the grouping by id & product was deduplicating, I’m sure it was primarily there for the MIN/MAX aggregates, none of which are used.
  3. NVL instead of an OR-ed sub-clause.
  4. OBJECT_NAMES rather than OBJECT_PAIRS since only the “name” field is used.
  5. NVL2 in the UNNEST means less duplication: if productStatus is present with say 20 fields and productId is present too, then you were repeating productId 20 times. (Before the grouping by product de-duplicated - cheaper to not generate the duplicates in the first place typically.) Also doesn’t need to be a LEFT UNNEST since the NVL2 will have a result always.

But since I don’t have example source data I am not 100% sure this is exactly what you’re after though.

4

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.