Selecting Two Document into One

kriticrm · October 10, 2018, 10:12am

I have two Documents of type booking and type details.

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"
                    }
                ]
            }
        ]
    }
]

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,
             }
         ],

     }
 ]

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"
                    }
                ]
            }
        ]
    }
]

vsr1 · October 10, 2018, 1:14pm

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

kriticrm · October 10, 2018, 2:33pm

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 .

vsr1 · October 10, 2018, 2:55pm

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>

kriticrm · October 10, 2018, 7:37pm

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,
                }
            ]
}

vsr1 · October 11, 2018, 12:31am

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" ;

kriticrm · October 11, 2018, 8:34am

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.

vsr1 · October 11, 2018, 12:55pm

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" ;

kriticrm · October 11, 2018, 1:13pm

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

vsr1 · October 11, 2018, 1:32pm

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

Topic		Replies	Views
N1ql query for joining two types of document inside a single bucket SQL++	1	1221	May 31, 2018
N1QL Query to join array fields with an array in another document SQL++ n1ql	2	566	January 11, 2023
Join + Selection SQL++ n1ql	2	525	January 17, 2020
Need help in writing the N1QL query SQL++ query , n1ql	3	199	June 20, 2024
How to join more two document in same bucket? SQL++ query , n1ql	13	1410	April 20, 2018

Selecting Two Document into One

Related topics