Cosmos DB JSON结构转换：无需指定数组索引的查询实现

1. Get Structured Output (Preserving extrasCoverServices Array) #

To avoid hardcoding array indexes and include all elements in extrasCoverServices (excluding the providers field), use an ARRAY expression with a subquery in your SELECT clause. This iterates over every entry in the extrasCoverServices array and projects only the fields you need:

SELECT 
    a.id,
    a.practiceId,
    ARRAY(
        SELECT 
            e.serviceTypeName,
            e.serviceTypeCode,
            e.serviceItems
        FROM e IN a.extrasCoverServices
    ) AS extrasCoverServices
FROM a
WHERE a.id = @documentId

How This Works: #

• The ARRAY(...) construct creates a new array by looping through each e in a.extrasCoverServices.
• For each element, we select only serviceTypeName, serviceTypeCode, and serviceItems (omitting providers entirely).
• No fixed indexes are used, so all entries in extrasCoverServices are included automatically.

2. Adjusting for Flattened Service Items + Customisations (Your Updated Function) #

Looking at your stored procedure, you’re trying to combine each serviceItem with its customisations into a flat list. To retain the parent serviceTypeName and serviceTypeCode context for each combined item, modify your query and processing logic like this:

Updated Query #

SELECT 
    a.id,
    a.practiceId,
    e.serviceTypeName,
    e.serviceTypeCode,
    ARRAY_CONCAT(
        [{"itemName": s.itemName, "itemNumber": s.itemNumber, "fee": s.fee, "isReferenceItem": s.isReferenceItem}],
        IS_DEFINED(s.customisations) ? s.customisations : []
    ) AS combinedItems
FROM a
JOIN e IN a.extrasCoverServices
JOIN s IN e.serviceItems
WHERE a.id = '${documentId}'

Updated Stored Procedure Logic #

function sample(documentId) {
    var collection = getContext().getCollection();
    var query = `
        SELECT 
            a.id,
            a.practiceId,
            e.serviceTypeName,
            e.serviceTypeCode,
            ARRAY_CONCAT(
                [{"itemName": s.itemName, "itemNumber": s.itemNumber, "fee": s.fee, "isReferenceItem": s.isReferenceItem}],
                IS_DEFINED(s.customisations) ? s.customisations : []
            ) AS combinedItems
        FROM a
        JOIN e IN a.extrasCoverServices
        JOIN s IN e.serviceItems
        WHERE a.id = '${documentId}'
    `;

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        query,
        function (err, feed, options) {
            if (err) throw err;
            if (!feed || !feed.length) {
                getContext().getResponse().setBody('no docs found');
                return;
            }

            // Flatten combinedItems and attach service context to each item
            var result = [];
            feed.forEach(doc => {
                doc.combinedItems.forEach(item => {
                    result.push({
                        id: doc.id,
                        practiceId: doc.practiceId,
                        serviceTypeName: doc.serviceTypeName,
                        serviceTypeCode: doc.serviceTypeCode,
                        ...item
                    });
                });
            });

            getContext().getResponse().setBody(result);
        }
    );

    if (!isAccepted) throw new Error('The query was not accepted by the server.');
}

Key Changes: #

• The query now includes serviceTypeName and serviceTypeCode in the projection, so each row carries that context.
• The stored procedure flattens the combinedItems array and merges the service metadata into every individual item, giving you a clean, flat list of all items (original + customised) with all required details.

内容的提问来源于stack exchange，提问作者user3603308