N1QL Join to embed a child document array into parent document

Hi, I am stuck on what seems like a relatively simple need but can’t seem to get this to work the way i want it.
I have two document types. Cust and Address. Cust to address is a one to many relationship.

{
  "custId": 123,
  "name": "storeAB",
  "type": "cust"
}

and

{
  "custId": "cust::123",
  "addId": 998,
  "dateStart": "01-01-2019",
  "type": "address"
}
{
  "custId": "cust::123",
  "addId": 555,
  "dateStart": "01-01-2017",
  "type": "address"
}

I need to join these two document types into a single customer doc. The doc should have all the cust attributes once and an array of the address document.
sample:

{
  "custId": 123,
  "name": "storeAB",
  "type": "cust",
    address: [
{
  "custId": "cust::123",
  "addId": 998,
  "dateStart": "01-01-2019",
  "type": "address"
},
{
  "custId": "cust::123",
  "addId": 555,
  "dateStart": "01-01-2017",
  "type": "address"
     }
  ]
}

How do i write a N1QL to make this happen? Thanks for your help.

I think I figured it out.

SELECT * 
FROM default cust
NEST default addressON KEY (address.custId) 
FOR cust

Also Checkout ANSI NEST Example 17 https://blog.couchbase.com/ansi-join-support-n1ql/

@amrish, @vsr1 - Would either of you post the CREATE INDEX statement for the example amrish posted here? It puzzling how the custId in the parent is an integer 123 yet the custId in the addresses is formatted string. How does 123 create a relationship to “cust::123”?

@vsr1 - I could not relate how the example you referenced in your link translates to the simple example amrish posted. Would you illustrate the index and the query required to achieve what amrish claims to have achieved with the example he posted here.

JG

Nevermind. I have figured it out.

For my scenario, I needed to join response object types to the request types to which they related. The resquest Id is embedded in each response.

I was seeking to join the response to these requests on the matching request_id.

Here is the query:

SELECT request, ARRAY_AGG(response) AS response FROM bucket_name response
JOIN bucket_name request ON KEYS response.request_id
where response.type like ‘processed-%-response’
GROUP BY request;

and here is the index required:

CREATE INDEX ix_processed_items ON bucket_name(response.request_id) WHERE ((response.type) like “processed-%-response”)

I used LIKE because there are multiple types of 'processed response and processed requests

This configuration works like a charm.

Thanks to all.

JG

SELECT * 
FROM default cust
NEST default address ON KEY (address.custId) 
FOR cust

CREATE INDEX ix1 ON default(custId); — for address

This is Index NEST. It matches META(cust).id = address.custId

CREATE INDEX ix1 ON default (name, custId) WHERE type = "cust";
CREATE INDEX ix2 ON default (custId) WHERE type = "address";

    SELECT c.* , a AS address
    FROM default AS c 
    NEST default  AS a  ON  a.custId = META(c).id 
    WHERE c.type = "cust" AND a.type = "address" AND c.name = "storeAB";

OR

   SELECT c.* , ARRAY_AGG(a) AS address
    FROM default AS c 
    JOIN default  AS a  ON  a.custId = META(c).id 
    WHERE c.type = "cust" AND a.type = "address" AND c.name = "storeAB"
    GROUP BY c;

you can also replace META©.id to c.type || “::”||TO_STRING(c. custId)

1 Like