Azure Cosmos query to convert into List

Question

This is my JSON data, which is stored into cosmos db

{
    "id": "e064a694-8e1e-4660-a3ef-6b894e9414f7",
    "Name": "Name",
    "keyData": {
        "Keys": [
            "Government",
            "Training",
            "support"
        ]
    }
}

Now I want to write a query to eliminate the keyData and get only the Keys (like below)

{
    "userid": "e064a694-8e1e-4660-a3ef-6b894e9414f7",
    "Name": "Name",
    "Keys"  :[
        "Government",
        "Training",
        "support"
    ]
}

So far I tried the query like

SELECT c.id,k.Keys  FROM c
JOIN k in c.keyPhraseBatchResult

Which is not working.

Update 1:

After trying with the Sajeetharan now I can able to get the result, but the issue it producing another JSON inside the Array.

Like

{
    "id": "ee885fdc-9951-40e2-b1e7-8564003cd554",
    "keys": [
        {
            "serving": "Government"
        },
        {
            "serving": "Training"
        },
        {
            "serving": "support"
        }
    ]
}

Is there is any way that extracts only the Array without having key value pari again?

{
    "userid": "e064a694-8e1e-4660-a3ef-6b894e9414f7",
    "Name": "Name",
    "Keys"  :[
        "Government",
        "Training",
        "support"
    ]
}

Sajeetharan · Accepted Answer · 2019-03-12T01:47:07.163

2

You could try this one,

SELECT C.id, ARRAY(SELECT VALUE serving FROM serving IN C.keyData.Keys) AS Keys FROM C

edited Mar 12 '19 at 01:47

answered Mar 11 '19 at 03:12

Sajeetharan

216,225
63
350
396

thanks, Sajeetharan for your answer, I tried your answer and facing other issue,Pls see my *Update 1* in the OP. could you pls help me – Jayendran Mar 11 '19 at 04:50
1

Try this instead: SELECT C.id, ARRAY(SELECT VALUE serving FROM serving IN C.keyData.Keys) AS Keys FROM C – Krishnan Sundaram Mar 11 '19 at 19:46
Thanks Krishnan and sajeetharan for your help. – Jayendran Mar 12 '19 at 04:36

score 1 · Answer 2 · answered Mar 11 '19 at 07:13

Please use cosmos db stored procedure to implement your desired format based on the @Sajeetharan's sql.

function sample() {
    var collection = getContext().getCollection();

    var isAccepted = collection.queryDocuments(
        collection.getSelfLink(),
        'SELECT C.id,ARRAY(SELECT serving FROM serving IN C.keyData.Keys) AS keys FROM C',
    function (err, feed, options) {
        if (err) throw err;
        if (!feed || !feed.length) {
            var response = getContext().getResponse();
            response.setBody('no docs found');
        }
        else {
            var response = getContext().getResponse();
            var map = {};
             for(var i=0;i<feed.length;i++){
                 var keyArray = feed[i].keys;
                 var array = [];
                 for(var j=0;j<keyArray.length;j++){
                    array.push(keyArray[j].serving)
                 }
                 feed[i].keys = array;
             }
            response.setBody(feed);
        }
    });

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

Output:

Thanks @jaygong upvoted since sajeetharan updated query worked now. But with your solution I learned how to achieve this using storproc. Thanks again ! — Jayendran, Mar 12 '19 at 04:38
@Jayendran Thank you.@sajeetharan's solution is very nice for you. — Jay Gong, Mar 12 '19 at 06:17

Azure Cosmos query to convert into List

2 Answers2