Selecting Two Document into One

n1ql

#1

I have two Documents of type booking and type details.

  1. Booking Document

    [
        {
            "booking_details": {
                "project": "w"
            },
            "booking_disp_id": "LT/FY18-19/B-1",
            "booking_id": "booking::1",
            "work_disp_id": "LT/FY18-19/W-1",
            "work_order_id": "work_order::1",
            "document_type": "booking",
            "samples": [
                {
                    "product_details_data": {
                        "product_detail::291": "Detail 1"
                    },
                    "product_disp_code": "BM-1",
                    "product_feasibility_data": {},
                    "product_id": "product::45",
                    "tests": [
                        {
                            "res_id": "res_1"
                        },
                        {
                            "res_id": "res_2"
                        },
                        {
                            "res_id": "res_3"
                        }
                    ]
                }
            ]
        },
        {
            "booking_details": {
                "project": "PN"
            },
            "booking_disp_id": "LT/FY18-19/B-2",
            "booking_id": "booking::2",
            "work_disp_id": "LT/FY18-19/W-2",
            "work_order_id": "work_order::2",
            "document_type": "booking",
            "samples": [
                {
                    "approved": true,
                    "product_details_data": {
                        "product_detail::4": "Source",
                        "product_detail::5": "Type"
                    },
                    "product_disp_code": "SM-1",
                    "product_feasibility_data": {
    
                    },
                    "product_id": "product::2",
                    "tests": [
                        {
                            "res_id": "res_4"
                        }
                    ]
                }
            ]
        },
        {
            "booking_details": {
                "project": "ABC"
            },
            "booking_disp_id": "LT/FY18-19/B-3",
            "booking_id": "booking::3",
            "work_disp_id": "LT/FY18-19/W-3",
            "work_order_id": "work_order::3",
            "document_type": "booking",
            "samples": [
                {
                    "product_details_data": {
                        "product_detail::291": "1",
                        "product_detail::292": "2"
                    },
                    "product_disp_code": "BM-2",
                    "product_feasibility_data": {
    
                    },
                    "product_id": "product::45",
                    "tests": [
                        {
                            "res_id": "res_5"
                        },
                        {
                            "res_id": "res_6"
                        }
                    ]
                }
            ]
        }
    ]
    
  2. Details Document

     [
         {
             "work_order_id": "work_order::1",
             "detail_id": "detail::1",
             "document_type": "details",
             "tests": [
                 {
                     "remarks": "",
                     "time": "2018-10-09T10:09:57.831Z",
                     "user_id": "user::1",
                     "calc": {
                         "sheet_1": "1",
                         "sheet_2": "2",
                         "sheet_3": "3",
                         "sheet_4": "4"
                     },
                     "expected_end": "2018-10-10",
                     "data": {},
                     "product_disp_code": "BM-1",
                     "product_id": "product::45",
                     "res_id": "res_1",
                     "attachment": [],
                     "images": [],
                     "start": "2018-10-09",
                     "status": 1,
                 },
                 {
                     "remarks": "",
                     "time": "2018-10-09T10:09:57.831Z",
                     "user_id": "user::1",
                     "calc": {
                         "sheet_5": "5",
                         "sheet_6": "6",
                         "sheet_7": "7",
                         "sheet_8": "8"
                     },
                     "expected_end": "2018-10-10",
                     "data": {},
                     "product_disp_code": "BM-1",
                     "product_id": "product::45",
                     "res_id": "res_2",
                     "attachment": [],
                     "images": [],
                     "start": "2018-10-09",
                     "status": 1,
                 }
             ]
         },
         {
             "work_order_id": "work_order::1",
             "detail_id": "detail::2",
             "document_type": "details",
             "tests": [
                 {
                     "remarks": "",
                     "time": "2018-10-09T10:09:57.831Z",
                     "user_id": "user::1",
                     "calc": {
                         "sheet_1": "1",
                         "sheet_3": "3",
                         "sheet_5": "5",
                         "sheet_9": "9"
                     },
                     "expected_end": "2018-10-10",
                     "data": {},
                     "product_disp_code": "BM-1",
                     "product_id": "product::45",
                     "res_id": "res_3",
                     "attachment": [],
                     "images": [],
                     "start": "2018-10-09",
                     "status": 1,
                 }
             ]
         },
         {
             "work_order_id": "work_order::2",
             "detail_id": "detail::3",
             "document_type": "details",
             "tests": [
                 {
                     "remarks": "",
                     "time": "2018-10-09T10:09:57.831Z",
                     "user_id": "user::1",
                     "calc": {
                         "sheet_1": "0",
                         "sheet_3": "0",
                         "sheet_5": "0",
                         "sheet_9": "0"
                     },
                     "expected_end": "2018-10-10",
                     "data": {},
                     "product_disp_code": "SM-1",
                     "product_id": "product::2",
                     "res_id": "res_4",
                     "attachment": [],
                     "images": [],
                     "start": "2018-10-09",
                     "status": 0,
                 }
             ]
         },
         {
             "work_order_id": "work_order::2",
             "detail_id": "detail::4",
             "document_type": "details",
             "tests": [
                 {
                     "remarks": "",
                     "time": "2018-10-09T10:09:57.831Z",
                     "user_id": "user::1",
                     "calc": {
                         "sheet_1": "1",
                         "sheet_3": "3",
                         "sheet_5": "5",
                         "sheet_9": "9"
                     },
                     "expected_end": "2018-10-10",
                     "data": {},
                     "product_disp_code": "SM-1",
                     "product_id": "product::2",
                     "res_id": "res_4",
                     "attachment": [],
                     "images": [],
                     "start": "2018-10-09",
                     "status": 1,
                 }
             ]
         },
         {
             "work_order_id": "work_order::3",
             "detail_id": "detail::5",
             "document_type": "details",
             "tests": [
                 {
                     "remarks": "",
                     "time": "2018-10-09T10:09:57.831Z",
                     "user_id": "user::1",
                     "calc": {
                         "sheet_10": "10",
                         "sheet_20": "20",
                         "sheet_30": "30",
                         "sheet_40": "40"
                     },
                     "expected_end": "2018-10-10",
                     "data": {},
                     "product_disp_code": "BM-2",
                     "product_id": "product::45",
                     "res_id": "res_5",
                     "attachment": [],
                     "images": [],
                     "start": "2018-10-09",
                     "status": 1,
                 }
             ],
    
         }
     ]
    
  3. Final Output

Select all booking Details and get res_ids and find if it has its corresponding details in details document with res_ids and status =1 and return full details in the booking details.

[
    {
        "booking_details": {
            "project": "w"
        },
        "booking_disp_id": "LT/FY18-19/B-1",
        "booking_id": "booking::1",
        "work_disp_id": "LT/FY18-19/W-1",
        "work_order_id": "work_order::1",
        "document_type": "booking",
        "samples": [
            {
                "product_details_data": {
                    "product_detail::291": "Detail 1"
                },
                "product_disp_code": "BM-1",
                "product_feasibility_data": {},
                "product_id": "product::45",
                "tests": [
                    {
                        "remarks": "",
                        "time": "2018-10-09T10:09:57.831Z",
                        "user_id": "user::1",
                        "calc": {
                            "sheet_1": "1",
                            "sheet_2": "2",
                            "sheet_3": "3",
                            "sheet_4": "4"
                        },
                        "expected_end": "2018-10-10",
                        "data": {},
                        "product_disp_code": "BM-1",
                        "product_id": "product::45",
                        "res_id": "res_1",
                        "attachment": [],
                        "images": [],
                        "start": "2018-10-09",
                        "status": 1,
                    },
                    {
                        "remarks": "",
                        "time": "2018-10-09T10:09:57.831Z",
                        "user_id": "user::1",
                        "calc": {
                            "sheet_5": "5",
                            "sheet_6": "6",
                            "sheet_7": "7",
                            "sheet_8": "8"
                        },
                        "expected_end": "2018-10-10",
                        "data": {},
                        "product_disp_code": "BM-1",
                        "product_id": "product::45",
                        "res_id": "res_2",
                        "attachment": [],
                        "images": [],
                        "start": "2018-10-09",
                        "status": 1,
                    },
                    {
                        "remarks": "",
                        "time": "2018-10-09T10:09:57.831Z",
                        "user_id": "user::1",
                        "calc": {
                            "sheet_1": "1",
                            "sheet_3": "3",
                            "sheet_5": "5",
                            "sheet_9": "9"
                        },
                        "expected_end": "2018-10-10",
                        "data": {},
                        "product_disp_code": "BM-1",
                        "product_id": "product::45",
                        "res_id": "res_3",
                        "attachment": [],
                        "images": [],
                        "start": "2018-10-09",
                        "status": 1,
                    }
                ]
            }
        ]
    },
    {
        "booking_details": {
            "project": "PN"
        },
        "booking_disp_id": "LT/FY18-19/B-2",
        "booking_id": "booking::2",
        "work_disp_id": "LT/FY18-19/W-2",
        "work_order_id": "work_order::2",
        "document_type": "booking",
        "samples": [
            {
                "approved": true,
                "product_details_data": {
                    "product_detail::4": "Source",
                    "product_detail::5": "Type"
                },
                "product_disp_code": "SM-1",
                "product_feasibility_data": {

                },
                "product_id": "product::2",
                "tests": [
                    {
                        "remarks": "",
                        "time": "2018-10-09T10:09:57.831Z",
                        "user_id": "user::1",
                        "calc": {
                            "sheet_1": "1",
                            "sheet_3": "3",
                            "sheet_5": "5",
                            "sheet_9": "9"
                        },
                        "expected_end": "2018-10-10",
                        "data": {},
                        "product_disp_code": "SM-1",
                        "product_id": "product::2",
                        "res_id": "res_4",
                        "attachment": [],
                        "images": [],
                        "start": "2018-10-09",
                        "status": 1,
                    }
                ]
            }
        ]
    },
    {
        "booking_details": {
            "project": "ABC"
        },
        "booking_disp_id": "LT/FY18-19/B-3",
        "booking_id": "booking::3",
        "work_disp_id": "LT/FY18-19/W-3",
        "work_order_id": "work_order::3",
        "document_type": "booking",
        "samples": [
            {
                "product_details_data": {
                    "product_detail::291": "1",
                    "product_detail::292": "2"
                },
                "product_disp_code": "BM-2",
                "product_feasibility_data": {

                },
                "product_id": "product::45",
                "tests": [
                    {
                        "remarks": "",
                        "time": "2018-10-09T10:09:57.831Z",
                        "user_id": "user::1",
                        "calc": {
                            "sheet_10": "10",
                            "sheet_20": "20",
                            "sheet_30": "30",
                            "sheet_40": "40"
                        },
                        "expected_end": "2018-10-10",
                        "data": {},
                        "product_disp_code": "BM-2",
                        "product_id": "product::45",
                        "res_id": "res_5",
                        "attachment": [],
                        "images": [],
                        "start": "2018-10-09",
                        "status": 1,
                    },
                    {
                        "res_id": "res_6"
                    }
                ]
            }
        ]
    }
]

#2

What is CB version? What Is Booking/Details Documents document keys. Is Booking Document/Detail Document is 1 with array or separate documents.


#3

CB version 4.6.4 .

Both are separate document with different keys.

For booking document keys are is booking_id/work_order_id.

For detail document key is detail_id. and it has a reference of work_order_id from booking document.

Within the Details document, there is an array namedtests which holds the all the details of a res_id from Booking.

So the details from Details document should be placed within Booking Document respectively based on the res_id and status =1 of Details Document .


#4

Can u post the following format for both the documents.

document type : booking
document key : <actual key>
document   : <actual document>

document type : detail
document key : <actual key>
document   : <actual document>

#5
document type: booking
document key: booking:: 1
document: {
    "booking_details": {
        "project": "w"
    },
    "booking_disp_id": "LT/FY18-19/B-1",
    "booking_id": "booking::1",
    "work_disp_id": "LT/FY18-19/W-1",
    "work_order_id": "work_order::1",
    "document_type": "booking",
    "samples": [
                {
                    "product_details_data": {
                        "product_detail::291": "Detail 1"
                    },
                    "product_disp_code": "BM-1",
                    "product_feasibility_data": {},
                    "product_id": "product::45",
                    "tests": [
                                {
                                    "res_id": "res_1"
                                },
                                {
                                    "res_id": "res_2"
                                },
                                {
                                    "res_id": "res_3"
                                }
                            ]
                }
            ]
}


document type: detail
document key: detail:: 1
document: 
{
    "work_order_id": "work_order::1",
    "detail_id": "detail::1",
    "document_type": "details",
    "tests": [
                {
                    "remarks": "",
                    "time": "2018-10-09T10:09:57.831Z",
                    "user_id": "user::1",
                    "calc": {
                            "sheet_1": "1",
                            "sheet_2": "2",
                            "sheet_3": "3",
                            "sheet_4": "4"
                        },
                    "expected_end": "2018-10-10",
                    "data": {},
                    "product_disp_code": "BM-1",
                    "product_id": "product::45",
                    "res_id": "res_1",
                    "attachment": [],
                    "images": [],
                    "start": "2018-10-09",
                    "status": 1,
                },
                {
                    "remarks": "",
                    "time": "2018-10-09T10:09:57.831Z",
                    "user_id": "user::1",
                    "calc": {
                            "sheet_5": "5",
                            "sheet_6": "6",
                            "sheet_7": "7",
                            "sheet_8": "8"
                        },
                    "expected_end": "2018-10-10",
                    "data": {},
                    "product_disp_code": "BM-1",
                    "product_id": "product::45",
                    "res_id": "res_2",
                    "attachment": [],
                    "images": [],
                    "start": "2018-10-09",
                    "status": 1,
                }
            ]
}

#6

CB 4.6.4 requires primary/foreign key relationship through document key (In 5.5 you can use ANSI JOIN).
Only option in CB 4.6.4 is join through arrays.

INSERT INTO default VALUES ("booking::1", { "booking_details": { "project": "w" }, "booking_disp_id": "LT/FY18-19/B-1", "booking_id": "booking::1", "work_disp_id": "LT/FY18-19/W-1", "work_order_id": "work_order::1", "document_type": "booking", "samples": [ { "product_details_data": { "product_detail::291": "Detail 1" }, "product_disp_code": "BM-1", "product_feasibility_data": {}, "product_id": "product::45", "tests": [ { "res_id": "res_1" }, { "res_id": "res_2" }, { "res_id": "res_3" } ] } ] });
INSERT INTO default VALUES ("booking::2",{ "booking_details": { "project": "PN" }, "booking_disp_id": "LT/FY18-19/B-2", "booking_id": "booking::2", "work_disp_id": "LT/FY18-19/W-2", "work_order_id": "work_order::2", "document_type": "booking", "samples": [ { "approved": true, "product_details_data": { "product_detail::4": "Source", "product_detail::5": "Type" }, "product_disp_code": "SM-1", "product_feasibility_data": null, "product_id": "product::2", "tests": [ { "res_id": "res_4" } ] } ] });
INSERT INTO default VALUES ("booking::3",{ "booking_details": { "project": "ABC" }, "booking_disp_id": "LT/FY18-19/B-3", "booking_id": "booking::3", "work_disp_id": "LT/FY18-19/W-3", "work_order_id": "work_order::3", "document_type": "booking", "samples": [ { "product_details_data": { "product_detail::291": "1", "product_detail::292": "2" }, "product_disp_code": "BM-2", "product_feasibility_data": null, "product_id": "product::45", "tests": [ { "res_id": "res_5" }, { "res_id": "res_6" } ] } ] });

INSERT INTO default VALUES ("detail::1",{ "work_order_id": "work_order::1", "detail_id": "detail::1", "document_type": "details", "tests": [ { "remarks": "", "time": "2018-10-09T10:09:57.831Z", "user_id": "user::1", "calc": { "sheet_1": "1", "sheet_2": "2", "sheet_3": "3", "sheet_4": "4" }, "expected_end": "2018-10-10", "data": {}, "product_disp_code": "BM-1", "product_id": "product::45", "res_id": "res_1", "attachment": [], "images": [], "start": "2018-10-09", "status": 1 }, { "remarks": "", "time": "2018-10-09T10:09:57.831Z", "user_id": "user::1", "calc": { "sheet_5": "5", "sheet_6": "6", "sheet_7": "7", "sheet_8": "8" }, "expected_end": "2018-10-10", "data": {}, "product_disp_code": "BM-1", "product_id": "product::45", "res_id": "res_2", "attachment": [], "images": [], "start": "2018-10-09", "status": 1 } ] });
INSERT INTO default VALUES ("detail::2",{ "work_order_id": "work_order::1", "detail_id": "detail::2", "document_type": "details", "tests": [ { "remarks": "", "time": "2018-10-09T10:09:57.831Z", "user_id": "user::1", "calc": { "sheet_1": "1", "sheet_3": "3", "sheet_5": "5", "sheet_9": "9" }, "expected_end": "2018-10-10", "data": {}, "product_disp_code": "BM-1", "product_id": "product::45", "res_id": "res_3", "attachment": [], "images": [], "start": "2018-10-09", "status": 1 } ] });
INSERT INTO default VALUES ("detail::3",{ "work_order_id": "work_order::2", "detail_id": "detail::3", "document_type": "details", "tests": [ { "remarks": "", "time": "2018-10-09T10:09:57.831Z", "user_id": "user::1", "calc": { "sheet_1": "0", "sheet_3": "0", "sheet_5": "0", "sheet_9": "0" }, "expected_end": "2018-10-10", "data": {}, "product_disp_code": "SM-1", "product_id": "product::2", "res_id": "res_4", "attachment": [], "images": [], "start": "2018-10-09", "status": 0 } ] });
INSERT INTO default VALUES ("detail::4",{ "work_order_id": "work_order::2", "detail_id": "detail::4", "document_type": "details", "tests": [ { "remarks": "", "time": "2018-10-09T10:09:57.831Z", "user_id": "user::1", "calc": { "sheet_1": "1", "sheet_3": "3", "sheet_5": "5", "sheet_9": "9" }, "expected_end": "2018-10-10", "data": {}, "product_disp_code": "SM-1", "product_id": "product::2", "res_id": "res_4", "attachment": [], "images": [], "start": "2018-10-09", "status": 1 } ] });
INSERT INTO default VALUES ("detail::5",{ "work_order_id": "work_order::3", "detail_id": "detail::5", "document_type": "details", "tests": [ { "remarks": "", "time": "2018-10-09T10:09:57.831Z", "user_id": "user::1", "calc": { "sheet_10": "10", "sheet_20": "20", "sheet_30": "30", "sheet_40": "40" }, "expected_end": "2018-10-10", "data": {}, "product_disp_code": "BM-2", "product_id": "product::45", "res_id": "res_5", "attachment": [], "images": [], "start": "2018-10-09", "status": 1 } ] });


SELECT b.*, ARRAY OBJECT_PUT(s,"tests", ARRAY
                                               (FIRST ds.resids[0]
                                                FOR ds IN details
                                                WHEN ds.work_order_id = b.work_order_id AND ds.res_id = ts.res_id
                                                END)
                                        FOR ts IN s.tests
                                        WHEN (ANY ds1 IN details SATISFIES ds1.work_order_id = b.work_order_id AND ds1.res_id = ts.res_id END)
                                        END)
            FOR s IN b.samples
            END AS samples
FROM default AS b
LET details = ( SELECT d.work_order_id, t.res_id, ARRAY_AGG(t) AS resids
                FROM default AS d
                UNNEST d.tests AS t
                WHERE d.document_type = "details" AND t.status = 1
                GROUP BY d.work_order_id, t.res_id)
WHERE b.document_type = "booking" ;

#7

I have Updated to CB version 5.5.

But the Same query Seems to be Deleting the previous tests array in Booking , if the res_id for that particular item is not Found in detail Document and returning [] in the tests array of Booking.

Any help is Appreciated.


#8

You can convert the query into ANSI JOIN

SELECT b.*, ARRAY OBJECT_PUT(s,"tests", ARRAY
                                               IFMISSING((FIRST ds.resids[0]
                                                FOR ds IN details
                                                WHEN ds.work_order_id = b.work_order_id AND ds.res_id = ts.res_id
                                                END),ts)
                                        FOR ts IN s.tests
                                        END)
            FOR s IN b.samples
            END AS samples
FROM default AS b
LET details = ( SELECT d.work_order_id, t.res_id, ARRAY_AGG(t) AS resids
                FROM default AS d
                UNNEST d.tests AS t
                WHERE d.document_type = "details" AND t.status = 1
                GROUP BY d.work_order_id, t.res_id)
WHERE b.document_type = "booking" ;

#9

I tried , But its giving me Error

select * from LNT as booking
unnest booking.samples as samples
unnest booking.samples as tests left join LNT as detail on booking.work_order_id = detail.work_order_id and 
ANY test IN detail.tests SATISFIES test.res_id = tests.res_id END AND ANY test IN detail.tests SATISFIES test.status = 1 END
where booking.document_type='booking'

Error : No index available for ANSI join term detail


#10

ANSI JOIN require index on right side of join too. You should read the article in previous posts and follow accordingly.
CREATE INDEX ix1 ON LNT(work)order_id);

Also combine two ANY cause otherwise meaning is different. single any means matches in same array element, two means matches condition across array elements.

ANY test IN detail.tests SATISFIES test.res_id = tests.res_id AND test.status = 1 END