How to write the following N!QL

SQL++
,
1

I have a buket with a single a document like below .

{
	"productStatus" :{

		"productID":"Status",
		"productID1":"Status1",
		"productID2":"Status2"
	}
}

The product ID is a foreign key for product records .
I am using the below query to get productName and ID

SELECT api_external.data.displayName AS productName
			,uuid AS productId
		FROM api_external USE KEYS(ARRAY "product::" || v FOR v IN OBJECT_NAMES(t1.productStatus) END)
from bucket as t1

I want to get status as well as part of the SQL like

SELECT api_external.data.displayName AS productName
			,uuid AS productId, 
                    t1.productStatus[uuid] as status 
		FROM api_external USE KEYS(ARRAY "product::" || v FOR v IN OBJECT_NAMES(t1.productStatus) END)
from bucket as t1 


result must look like
{

"products": [
      {
        "productId": "ID",
        "productName": "Name",
        "status": "status"
      },
	   {
        "productId": "ID1",
        "productName": "Name1",
        "status": "status1"
      }
    ]
  }

How do i do it ?

2 
SELECT  ae.data.displayName AS productName,
        productId,
        ps.[productId] AS status
FROM mybucket AS ps USE KEY "productStatus_docid"
JOIN api_external AS ae USE KEYS (ARRAY "product::" || n FOR n:v IN ps.productStatus END)
LET productId = SUBSTR(META(ae).id,9);

If need single object of products as ARRAY

SELECT (SELECT ae.data.displayName AS productName,
               productId,
               ps.[productId] AS status
       FROM api_external AS ae USE KEYS (ARRAY "product::" || n FOR n:v IN ps.productStatus END)
       LET productId = SUBSTR(META(ae).id,9)) AS products;
FROM mybucket AS ps USE KEY "productStatus_docid";

SELECT (SELECT ae.data.displayName AS productName,
               productId,
               ps.[productId] AS status
       FROM mybucket AS ps USE KEY "productStatus_docid"
       JOIN api_external AS ae USE KEYS (ARRAY "product::" || n FOR n:v IN ps.productStatus END)
       LET productId = SUBSTR(META(ae).id,9)) AS products;
3

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