What is the best way to join 2 different Docs in a none to many relationship

SQL++
1

Here is my situation , i have a Doc type of _type = “listing’ and a key ParcelNumber that also exists in my _type = 'farm” and the ParcelNumber is called APN i farm doc. As it is not a 1 to 1 join i need to find a way to get all docs from farm and the newest from listing as there can be none or 1 or more docs of type listing and ParcelNumber. And to determne which If there is many i need to get record with highest value of Record.Dates.ModificationTimestamp.

The problem is that its not a basic join nd not sure i can solve it with a subquery like when i want to get a newest doc in an array of a doc. Also in both cases ParcelNumber and APN is not a KEY but just a regular indexed field.

2

Check Example 17 ANSI JOIN Support in N1QL - The Couchbase Blog
After NEST (Avoids JOIN and AGGREGATION) you get ARRAY of listing documents for each farm document.
Then you use subquery expression and select latest document.

SELECT f AS farm, 
               (SELECT RAW p1
                 FROM p AS p1
                 ORDER BY p1.Record.Dates.ModificationTimestamp DESC
                 LIMIT 1)[0] AS lisiting
FROM mybucket AS f 
LEFT NEST mybucket AS p ON f.ParcelNumber = p.ParcelNumber AND p._type = "listing"
WHERE f._type = "farm" 
              AND .....
3

Ok i tried this but it isnt working as expected…
I adjusted the sample to my buckets and fields but it doesn’t get me anything for the listing part

SELECT f.Record.PropertyAddress AS farm,
       (
           SELECT RAW p1
           FROM p AS p1
           ORDER BY p1.Record.Dates.ModificationTimestamp DESC
           LIMIT 1)[0] AS lisiting
FROM Contacts AS f LEFT NEST rets AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "")
    AND p._type = "listing"
WHERE f._type = "farm"
    AND f.Record.apn ="65338104"
LIMIT 2

I get the farm Record.PropertyAddress but nothing from the listing query . Where and how would i specify the fields from listing to show ? I see the SELECT RAW p1 but that dosn show anything in result

4

It will work. Check field names.

The following is working.

WITH data AS ( [{"_type":"farm","Record":{"apn":"65338104", "PropertyAddress": "1 Infinite Loop; Cupertino, CA 95014"}},
                {"_type":"farm","Record":{"apn":"65338108", "PropertyAddress": "2 Infinite Loop; Cupertino, CA 95014"}},
                {"_type":"listing", "ParcelNumber":"653-38-104", "Record":{"Dates":{"ModificationTimestamp":"2022-03-28T21:26:45.594-07:00"}}},
                {"_type":"listing", "ParcelNumber":"653-38-104", "Record":{"Dates":{"ModificationTimestamp":"2022-02-28T21:26:45.594-07:00"}}}
               ])
SELECT f.Record.PropertyAddress AS farm,
       (
           SELECT p1.ParcelNumber, p1.Record
           FROM p AS p1
           ORDER BY p1.Record.Dates.ModificationTimestamp DESC
           LIMIT 1)[0] AS lisiting

FROM data AS f 
LEFT NEST data AS p ON f.Record.apn = REPLACE(p.ParcelNumber, "-", "")
                       AND p._type = "listing"
WHERE f._type = "farm"
LIMIT 2;
5

Yes thanks that worked, it was late and the sample provided didn’t reflect the correct _type for listing that’s why i had to change it and also create index for the Replace(ParcelNumber)