SQL Parse JSON

(0) Share

Report

Posted on by nick9one1

299

I have a powerapp that needs to collect more than 2000 rows from SQL, so I'm trying to do this in Power Automate;

this is one row of the JSON

{
 "statusCode": 200,
 "headers": {
 "Pragma": "no-cache",
 "Transfer-Encoding": "chunked",
 "Vary": "Accept-Encoding",
 "x-ms-request-id": "xxxxx-cf8e-4e7f-8abf-519757e153b2",
 "Strict-Transport-Security": "max-age=31536000; includeSubDomains",
 "X-Content-Type-Options": "nosniff",
 "X-Frame-Options": "DENY",
 "Timing-Allow-Origin": "*",
 "x-ms-apihub-cached-response": "true",
 "x-ms-apihub-obo": "false",
 "Cache-Control": "no-store, no-cache",
 "Date": "Tue, 08 Aug 2023 09:29:04 GMT",
 "Content-Type": "application/json; charset=utf-8; odata.metadata=minimal",
 "Expires": "-1",
 "Content-Length": "3149"
 },
 "body": {
 "@odata.context": "https://flow-apim-europe-001-northeurope-01.azure-apim.net/apim/sql/xxxxxxxc243afac420a5344f795bc/$metadata#datasets('default%2Cdefault')/tables('%5Bxxxxxx%5D.%5BCurrent%5D')/items",
 "value": [
 {
 "@odata.etag": "",
 "ItemInternalId": "xxxxx-7ed7-4bae-91d4-892576dd8be1",
 "FirstName": "xxxx",
 "LastName": "xxxx",
 "Refund": xxx,
 "AccountName": "xxxx",
 "AccountNumber": "xxxx",
 "SortCode": "xxxx",
 "RoomNumber": "xxxx",
 "HasFutureBooking": "Yes",
 "ActualDepartureDate": "2023-06-13T00:00:00",
 "ExpectedDepartureDate": "2023-06-13T00:00:00",
 "Site": "xxxxx",
 "Session": "xxxxx",
 "StatusId": 2,
 "RawId": 42242,
 "CheckValues": "xxxx",
 "ImportDate": "2023-07-20T12:00:00",
 "RefundId": 27838,
 "StudentId": "xxxx",
 "SPV": "xxxx"
 }
 
 ]
 }
}

and the schema

{
 "type": "object",
 "properties": {
 "statusCode": {
 "type": "integer"
 },
 "headers": {
 "type": "object",
 "properties": {
 "Pragma": {
 "type": "string"
 },
 "Transfer-Encoding": {
 "type": "string"
 },
 "Vary": {
 "type": "string"
 },
 "x-ms-request-id": {
 "type": "string"
 },
 "Strict-Transport-Security": {
 "type": "string"
 },
 "X-Content-Type-Options": {
 "type": "string"
 },
 "X-Frame-Options": {
 "type": "string"
 },
 "Timing-Allow-Origin": {
 "type": "string"
 },
 "x-ms-apihub-cached-response": {
 "type": "string"
 },
 "x-ms-apihub-obo": {
 "type": "string"
 },
 "Cache-Control": {
 "type": "string"
 },
 "Date": {
 "type": "string"
 },
 "Content-Type": {
 "type": "string"
 },
 "Expires": {
 "type": "string"
 },
 "Content-Length": {
 "type": "string"
 }
 }
 },
 "body": {
 "type": "object",
 "properties": {
 "@@odata.context": {
 "type": "string"
 },
 "value": {
 "type": "array",
 "items": {
 "type": "object",
 "properties": {
 "@@odata.etag": {
 "type": "string"
 },
 "ItemInternalId": {
 "type": "string"
 },
 "FirstName": {
 "type": "string"
 },
 "LastName": {
 "type": "string"
 },
 "Refund": {
 "type": "integer"
 },
 "AccountName": {
 "type": "string"
 },
 "AccountNumber": {
 "type": "string"
 },
 "SortCode": {
 "type": "string"
 },
 "RoomNumber": {
 "type": "string"
 },
 "HasFutureBooking": {
 "type": "string"
 },
 "ActualDepartureDate": {
 "type": "string"
 },
 "ExpectedDepartureDate": {
 "type": "string"
 },
 "Site": {
 "type": "string"
 },
 "Session": {
 "type": "string"
 },
 "StatusId": {
 "type": "integer"
 },
 "RawId": {
 "type": "integer"
 },
 "CheckValues": {
 "type": "string"
 },
 "ImportDate": {
 "type": "string"
 },
 "RefundId": {
 "type": "integer"
 },
 "StudentId": {
 "type": "string"
 },
 "SPV": {
 "type": "string"
 },
 "SwiftCode": {
 "type": "string"
 },
 "IBAN": {
 "type": "string"
 }
 },
 "required": [

 
 ]
 }
 }
 }
 }
 }
}

In powerapps my Collect has one row in the table

Categories:

General topics

I have the same question (0)

All responses (5)

Answers (0)

v-jefferni on at

Like (0)

Report
Hi @nick9one1 ,

JSON schema seems incorrect, the outputs of Get rows should be of type array. Please try below schema instead:

{ "type": "array", "items": { "type": "object", "properties": { "@@odata.etag": { "type": "string" }, "ItemInternalId": { "type": "string" }, "FirstName": { "type": "string" }, "LastName": { "type": "string" }, "Refund": { "type": "string" }, "AccountName": { "type": "string" }, "AccountNumber": { "type": "string" }, "SortCode": { "type": "string" }, "RoomNumber": { "type": "string" }, "HasFutureBooking": { "type": "string" }, "ActualDepartureDate": { "type": "string" }, "ExpectedDepartureDate": { "type": "string" }, "Site": { "type": "string" }, "Session": { "type": "string" }, "StatusId": { "type": "integer" }, "RawId": { "type": "integer" }, "CheckValues": { "type": "string" }, "ImportDate": { "type": "string" }, "RefundId": { "type": "integer" }, "StudentId": { "type": "string" }, "SPV": { "type": "string" } }, "required": [] } }

Best regards,

Was this reply helpful? Yes No
earribasb 74 on at

Like (0)

Report

The problem is not with Parse JSON. Therer is an OoO limit for get queries in Power Platform connectors because of possible size responses.

You can change the limit by paginating with the action settings:

In the "SQL Get Rows (V2)" part of the flow, go to the 3 dots and Settings. Force PAGINATION to ON and set to max 5000 (or whatever). Now you should retrieve all elements.

Another way is to do a "manual pagination" based in the index with a do-until action. But is more complex...

Was this reply helpful? Yes No
nick9one1 299 on at

Like (0)

Report

this isn't the problem. By using power automate/logic apps you can pass a jason string into the app larger than the pagination limit.

Was this reply helpful? Yes No
SanmeshG 1,947 Moderator on at

Like (0)

Report

Hi @nick9one1 ,

Check what is the output you are getting when you have one row(array or object),

Maybe consider generating the schema again and add it in your response action.

If its an object you might want to convert it to an array (but this should be only when only one row is there) else you send it as it is , as I guess it will be an array otherwise. The value should ideally give you an array even if its one row.

Thanks,

Was this reply helpful? Yes No
v-jefferni on at

Like (0)

Report

Hi @nick9one1 ,

Have you tried my suggestion? You can see I was able to get the data rows using the schema in my post.

Best regards,

Was this reply helpful? Yes No