SqlQuery in Azure Function ARRAY_CONTAINS not work
0
I defined a function in cosmo db defined as follows. In the editor the query works but when I run the function it return 500 Internal Server Error.
My document:
{
"user": "428",
"year": "2019",
"id": "1",
"dataType": "LineString",
"dataCategory": "realPath",
"tripNumber": "A02232",
"currentCoordinate": [
13.845224,
43.02356
],
"deliveries": [
{
"devNumber": "001",
"unloadSeq": "1",
"currentDev": "1",
"email": "punto1@mail.it",
"targetCoordinate": [
13.965224,
43.95356
],
"coordinates": [
[
13.790663,
43.028926
],
[
13.791447,
43.029169
],
[
13.792198,
43.029561
],
[
13.793775,
43.030549
],
[
13.794601,
43.0312
],
[
13.795577,
43.031835
],
[
13.797047,
43.032737
],
[
13.797605,
43.033153
],
[
13.798249,
43.033647
],
[
13.798732,
43.03367
],
[
13.800126,
43.033678
],
[
13.801661,
43.033725
],
[
13.802755,
43.034172
],
[
13.845224,
43.02356
]
]
},
{
"devNumber": "008",
"unloadSeq": "2",
"currentDev": "0",
"email": "punto2@mail.it",
"targetCoordinate": [
13.995224,
43.99356
],
"coordinates":
}
],
"_rid": "3pRjAIHZRNUBAAAAAAAAAA==",
"_self": "dbs/3pRjAA==/colls/3pRjAIHZRNU=/docs/3pRjAIHZRNUBAAAAAAAAAA==/",
"_etag": ""00006b08-0000-0000-0000-5c2def460000"",
"_attachments": "attachments/",
"_ts": 1546514246
}
Query in editor it's ok:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {"currentDev": "1", "email": "punto1@mail.it"}, true)
Query in function.json don't work:
{
"bindings": [
{
"authLevel": "function",
"name": "req",
"type": "httpTrigger",
"direction": "in",
"methods": [
"get",
"post"
],
"route": "getRoutes/{emailpar}"
},
{
"name": "$return",
"type": "http",
"direction": "out"
},
{
"type": "cosmosDB",
"name": "inDocuments",
"databaseName": "cdb-01",
"collectionName": "myCollection",
"connectionStringSetting": "mpn_COSMOSDB",
"direction": "in",
"sqlQuery": "SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: {emailpar}}, true)"
}
]
}
I think it's a syntax problem to defining the parameter and the object to be searched within the array through the brackets {}.
azure-functions azure-cosmosdb asked Jan 3 at 16:00
Tomb86Tomb86
135
add a comment |
0
I defined a function in cosmo db defined as follows. In the editor the query works but when I run the function it return 500 Internal Server Error.
My document:
{
"user": "428",
"year": "2019",
"id": "1",
"dataType": "LineString",
"dataCategory": "realPath",
"tripNumber": "A02232",
"currentCoordinate": [
13.845224,
43.02356
],
"deliveries": [
{
"devNumber": "001",
"unloadSeq": "1",
"currentDev": "1",
"email": "punto1@mail.it",
"targetCoordinate": [
13.965224,
43.95356
],
"coordinates": [
[
13.790663,
43.028926
],
[
13.791447,
43.029169
],
[
13.792198,
43.029561
],
[
13.793775,
43.030549
],
[
13.794601,
43.0312
],
[
13.795577,
43.031835
],
[
13.797047,
43.032737
],
[
13.797605,
43.033153
],
[
13.798249,
43.033647
],
[
13.798732,
43.03367
],
[
13.800126,
43.033678
],
[
13.801661,
43.033725
],
[
13.802755,
43.034172
],
[
13.845224,
43.02356
]
]
},
{
"devNumber": "008",
"unloadSeq": "2",
"currentDev": "0",
"email": "punto2@mail.it",
"targetCoordinate": [
13.995224,
43.99356
],
"coordinates":
}
],
"_rid": "3pRjAIHZRNUBAAAAAAAAAA==",
"_self": "dbs/3pRjAA==/colls/3pRjAIHZRNU=/docs/3pRjAIHZRNUBAAAAAAAAAA==/",
"_etag": ""00006b08-0000-0000-0000-5c2def460000"",
"_attachments": "attachments/",
"_ts": 1546514246
}
Query in editor it's ok:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {"currentDev": "1", "email": "punto1@mail.it"}, true)
Query in function.json don't work:
{
"bindings": [
{
"authLevel": "function",
"name": "req",
"type": "httpTrigger",
"direction": "in",
"methods": [
"get",
"post"
],
"route": "getRoutes/{emailpar}"
},
{
"name": "$return",
"type": "http",
"direction": "out"
},
{
"type": "cosmosDB",
"name": "inDocuments",
"databaseName": "cdb-01",
"collectionName": "myCollection",
"connectionStringSetting": "mpn_COSMOSDB",
"direction": "in",
"sqlQuery": "SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: {emailpar}}, true)"
}
]
}
I think it's a syntax problem to defining the parameter and the object to be searched within the array through the brackets {}.
azure-functions azure-cosmosdb asked Jan 3 at 16:00
Tomb86Tomb86
135
What is your partition key property?
– Nick Chapsas
Jan 3 at 16:08
There are two things that can be wrong. First I would recommend adding single quotes around the email property so many the query look like this:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: '{emailpar}'}, true)
– Nick Chapsas
Jan 3 at 16:12
Second, you probably need to escape the curly brackets that don't refer to a string interpolated property. You can probably do that by adding an extra curly bracket so making the string look like this:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: '{emailpar}'}}, true)
– Nick Chapsas
Jan 3 at 16:13
Thanks @NickChapsas it works:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)My partition key are id/user but is not perfect. I'm still trying to figure out how to choose it so you do not have hot keys
– Tomb86
Jan 3 at 16:52
Awesome. I wrote up the answer. Please accept it.
– Nick Chapsas
Jan 3 at 17:37
add a comment |
0
0
0
I defined a function in cosmo db defined as follows. In the editor the query works but when I run the function it return 500 Internal Server Error.
My document:
{
"user": "428",
"year": "2019",
"id": "1",
"dataType": "LineString",
"dataCategory": "realPath",
"tripNumber": "A02232",
"currentCoordinate": [
13.845224,
43.02356
],
"deliveries": [
{
"devNumber": "001",
"unloadSeq": "1",
"currentDev": "1",
"email": "punto1@mail.it",
"targetCoordinate": [
13.965224,
43.95356
],
"coordinates": [
[
13.790663,
43.028926
],
[
13.791447,
43.029169
],
[
13.792198,
43.029561
],
[
13.793775,
43.030549
],
[
13.794601,
43.0312
],
[
13.795577,
43.031835
],
[
13.797047,
43.032737
],
[
13.797605,
43.033153
],
[
13.798249,
43.033647
],
[
13.798732,
43.03367
],
[
13.800126,
43.033678
],
[
13.801661,
43.033725
],
[
13.802755,
43.034172
],
[
13.845224,
43.02356
]
]
},
{
"devNumber": "008",
"unloadSeq": "2",
"currentDev": "0",
"email": "punto2@mail.it",
"targetCoordinate": [
13.995224,
43.99356
],
"coordinates":
}
],
"_rid": "3pRjAIHZRNUBAAAAAAAAAA==",
"_self": "dbs/3pRjAA==/colls/3pRjAIHZRNU=/docs/3pRjAIHZRNUBAAAAAAAAAA==/",
"_etag": ""00006b08-0000-0000-0000-5c2def460000"",
"_attachments": "attachments/",
"_ts": 1546514246
}
Query in editor it's ok:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {"currentDev": "1", "email": "punto1@mail.it"}, true)
Query in function.json don't work:
{
"bindings": [
{
"authLevel": "function",
"name": "req",
"type": "httpTrigger",
"direction": "in",
"methods": [
"get",
"post"
],
"route": "getRoutes/{emailpar}"
},
{
"name": "$return",
"type": "http",
"direction": "out"
},
{
"type": "cosmosDB",
"name": "inDocuments",
"databaseName": "cdb-01",
"collectionName": "myCollection",
"connectionStringSetting": "mpn_COSMOSDB",
"direction": "in",
"sqlQuery": "SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: {emailpar}}, true)"
}
]
}
I think it's a syntax problem to defining the parameter and the object to be searched within the array through the brackets {}.
azure-functions azure-cosmosdb asked Jan 3 at 16:00
Tomb86Tomb86
135
I defined a function in cosmo db defined as follows. In the editor the query works but when I run the function it return 500 Internal Server Error.
My document:
{
"user": "428",
"year": "2019",
"id": "1",
"dataType": "LineString",
"dataCategory": "realPath",
"tripNumber": "A02232",
"currentCoordinate": [
13.845224,
43.02356
],
"deliveries": [
{
"devNumber": "001",
"unloadSeq": "1",
"currentDev": "1",
"email": "punto1@mail.it",
"targetCoordinate": [
13.965224,
43.95356
],
"coordinates": [
[
13.790663,
43.028926
],
[
13.791447,
43.029169
],
[
13.792198,
43.029561
],
[
13.793775,
43.030549
],
[
13.794601,
43.0312
],
[
13.795577,
43.031835
],
[
13.797047,
43.032737
],
[
13.797605,
43.033153
],
[
13.798249,
43.033647
],
[
13.798732,
43.03367
],
[
13.800126,
43.033678
],
[
13.801661,
43.033725
],
[
13.802755,
43.034172
],
[
13.845224,
43.02356
]
]
},
{
"devNumber": "008",
"unloadSeq": "2",
"currentDev": "0",
"email": "punto2@mail.it",
"targetCoordinate": [
13.995224,
43.99356
],
"coordinates":
}
],
"_rid": "3pRjAIHZRNUBAAAAAAAAAA==",
"_self": "dbs/3pRjAA==/colls/3pRjAIHZRNU=/docs/3pRjAIHZRNUBAAAAAAAAAA==/",
"_etag": ""00006b08-0000-0000-0000-5c2def460000"",
"_attachments": "attachments/",
"_ts": 1546514246
}
Query in editor it's ok:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {"currentDev": "1", "email": "punto1@mail.it"}, true)
Query in function.json don't work:
{
"bindings": [
{
"authLevel": "function",
"name": "req",
"type": "httpTrigger",
"direction": "in",
"methods": [
"get",
"post"
],
"route": "getRoutes/{emailpar}"
},
{
"name": "$return",
"type": "http",
"direction": "out"
},
{
"type": "cosmosDB",
"name": "inDocuments",
"databaseName": "cdb-01",
"collectionName": "myCollection",
"connectionStringSetting": "mpn_COSMOSDB",
"direction": "in",
"sqlQuery": "SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: {emailpar}}, true)"
}
]
}
I think it's a syntax problem to defining the parameter and the object to be searched within the array through the brackets {}.
azure-functions azure-cosmosdb azure-functions azure-cosmosdb asked Jan 3 at 16:00
Tomb86Tomb86
135
asked Jan 3 at 16:00
Tomb86Tomb86
135
asked Jan 3 at 16:00
Tomb86Tomb86
135
asked Jan 3 at 16:00
Tomb86Tomb86
135
asked Jan 3 at 16:00
Tomb86Tomb86
135
135
What is your partition key property?
– Nick Chapsas
Jan 3 at 16:08
There are two things that can be wrong. First I would recommend adding single quotes around the email property so many the query look like this:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: '{emailpar}'}, true)
– Nick Chapsas
Jan 3 at 16:12
Second, you probably need to escape the curly brackets that don't refer to a string interpolated property. You can probably do that by adding an extra curly bracket so making the string look like this:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: '{emailpar}'}}, true)
– Nick Chapsas
Jan 3 at 16:13
Thanks @NickChapsas it works:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)My partition key are id/user but is not perfect. I'm still trying to figure out how to choose it so you do not have hot keys
– Tomb86
Jan 3 at 16:52
Awesome. I wrote up the answer. Please accept it.
– Nick Chapsas
Jan 3 at 17:37
add a comment |
What is your partition key property?
– Nick Chapsas
Jan 3 at 16:08
There are two things that can be wrong. First I would recommend adding single quotes around the email property so many the query look like this:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: '{emailpar}'}, true)
– Nick Chapsas
Jan 3 at 16:12
Second, you probably need to escape the curly brackets that don't refer to a string interpolated property. You can probably do that by adding an extra curly bracket so making the string look like this:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: '{emailpar}'}}, true)
– Nick Chapsas
Jan 3 at 16:13
Thanks @NickChapsas it works:SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)My partition key are id/user but is not perfect. I'm still trying to figure out how to choose it so you do not have hot keys
– Tomb86
Jan 3 at 16:52
Awesome. I wrote up the answer. Please accept it.
– Nick Chapsas
Jan 3 at 17:37
What is your partition key property?
– Nick Chapsas
Jan 3 at 16:08
What is your partition key property?
– Nick Chapsas
Jan 3 at 16:08
There are two things that can be wrong. First I would recommend adding single quotes around the email property so many the query look like this:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: '{emailpar}'}, true)– Nick Chapsas
Jan 3 at 16:12
There are two things that can be wrong. First I would recommend adding single quotes around the email property so many the query look like this:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: '{emailpar}'}, true)– Nick Chapsas
Jan 3 at 16:12
Second, you probably need to escape the curly brackets that don't refer to a string interpolated property. You can probably do that by adding an extra curly bracket so making the string look like this:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: '{emailpar}'}}, true)– Nick Chapsas
Jan 3 at 16:13
Second, you probably need to escape the curly brackets that don't refer to a string interpolated property. You can probably do that by adding an extra curly bracket so making the string look like this:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: '{emailpar}'}}, true)– Nick Chapsas
Jan 3 at 16:13
Thanks @NickChapsas it works:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true) My partition key are id/user but is not perfect. I'm still trying to figure out how to choose it so you do not have hot keys– Tomb86
Jan 3 at 16:52
Thanks @NickChapsas it works:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true) My partition key are id/user but is not perfect. I'm still trying to figure out how to choose it so you do not have hot keys– Tomb86
Jan 3 at 16:52
Awesome. I wrote up the answer. Please accept it.
– Nick Chapsas
Jan 3 at 17:37
Awesome. I wrote up the answer. Please accept it.
– Nick Chapsas
Jan 3 at 17:37
add a comment |
1 Answer
1
active
oldest
votes
0
You need to escape the non parameter string interpolated object of the sql query string. You can do that by adding an extra curly brace in each reference.
This means that yous query string must be:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)
answered Jan 3 at 17:36
Nick ChapsasNick Chapsas
3,2211517
add a comment |
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025797%2fsqlquery-in-azure-function-array-contains-not-work%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
You need to escape the non parameter string interpolated object of the sql query string. You can do that by adding an extra curly brace in each reference.
This means that yous query string must be:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)
answered Jan 3 at 17:36
Nick ChapsasNick Chapsas
3,2211517
add a comment |
0
You need to escape the non parameter string interpolated object of the sql query string. You can do that by adding an extra curly brace in each reference.
This means that yous query string must be:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)
answered Jan 3 at 17:36
Nick ChapsasNick Chapsas
3,2211517
add a comment |
0
0
0
You need to escape the non parameter string interpolated object of the sql query string. You can do that by adding an extra curly brace in each reference.
This means that yous query string must be:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)
answered Jan 3 at 17:36
Nick ChapsasNick Chapsas
3,2211517
You need to escape the non parameter string interpolated object of the sql query string. You can do that by adding an extra curly brace in each reference.
This means that yous query string must be:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)
answered Jan 3 at 17:36
Nick ChapsasNick Chapsas
3,2211517
answered Jan 3 at 17:36
Nick ChapsasNick Chapsas
3,2211517
answered Jan 3 at 17:36
Nick ChapsasNick Chapsas
3,2211517
answered Jan 3 at 17:36
Nick ChapsasNick Chapsas
3,2211517
3,2211517
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025797%2fsqlquery-in-azure-function-array-contains-not-work%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
What is your partition key property?
– Nick Chapsas
Jan 3 at 16:08
There are two things that can be wrong. First I would recommend adding single quotes around the email property so many the query look like this:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {currentDev: '1', email: '{emailpar}'}, true)– Nick Chapsas
Jan 3 at 16:12
Second, you probably need to escape the curly brackets that don't refer to a string interpolated property. You can probably do that by adding an extra curly bracket so making the string look like this:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: '{emailpar}'}}, true)– Nick Chapsas
Jan 3 at 16:13
Thanks @NickChapsas it works:
SELECT * FROM Trip c where ARRAY_CONTAINS(c.deliveries, {{currentDev: '1', email: {emailpar}}}, true)My partition key are id/user but is not perfect. I'm still trying to figure out how to choose it so you do not have hot keys– Tomb86
Jan 3 at 16:52
Awesome. I wrote up the answer. Please accept it.
– Nick Chapsas
Jan 3 at 17:37