Need help writing a N1QL query

SQL++
,
1

I have the below query that works

SELECT m.appName AS name,
        m.uuid AS id,
		m.companyId, -- look up in db by appending "company::" and populate data.companyName
        m.description,
        m.env,
        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 [ "37a721e90f8542e387ffeb9fd5571854","b96ff06a127247659158bbb9a9e0e150","b3340e13ec2147498aee8497fd72c934","ad1a06d4ed804d4892eafecf13690eab" ] 
        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.companyId, pa.data.fourthParty, i1.data.env, i1.data.productStatus , i1.createdTs,i1.updatedTs, i1.data.productId , i1.data.status }])[1]

Here m.companyId in the 3rd line is a document key in the same database bucket api_external

appending "company::"m.companyId I need to look in the same backet and need to populate data.companyName instead of companyId. How do i do that .

2

You could simply add:

...
m.companyId,
(SELECT RAW data.companyName FROM api_external ap2 USE KEYS ["company::"||to_string(m.companyId)])[0] companyName,
m.description,
...

to your select list.

HTH.

3

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