Selecting Two Document into One

SQL++
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