Cannot use query.data as source for JSON SQL as it is not an array

App Building
1

So I have noticed that query data in not an array itself. It looks like this:

{
    "line_item_id": [375,380,370],
    "invoice_id": ["210406ROD-2","210406ROD-2","210406ROD-2"],
    "sku": ["ANY-STK-0001","ANY-STK-0006","CAL-PAT-0006"]
}

So if I want to say count how many results returned I do this query1.data.line_item_id.length instead of this query1.data.length.

All other data objects (selectedRow.data, changeset etc.) are actually arrays. I have found my way around this until now.

I cannot use query.data in a Query JSON with SQL resource type like this:

select * 
from {{qryLineItemsSelect.rawData}}
where count > 0

I get no results. But there are code samples in the docs and on the forums that do exactly that. Now if I transform query.data into an actual array of objects, it works.

So ummm, what in the devil am I doing wrong? Was there some master setting I bumped that transforms all of my query results to this object rather than array format?

2

Ok, finally got a handle on the proper terminology for this so I could do an intelligent search and found that SQL queries return an object of arrays. Query JSON with SQL works with an array of objects. I found the formatDataAsArray() function so I was able to put this in the query’s transformer to get everything working properly:

return formatDataAsArray(data)

May I recommend a note about this in the doc pertaining to Query JSON with SQL?

4 Likes
3

Hi @bradlymathews!

Awesome, glad you were able to find that function :blush: I'd definitely be happy to add to our docs to make this easier to discover. We do have this little callout at the bottom of the docs—is there anything I can do to make it clearer?

1428Ă—890 60.1 KB


4

Thanks, I also learned from Justin today about the Suggest Edits at the top right of the page. I will use that as well.

5

❤️ Yes, PLEASE. Excited to start merging in your changes!