Json_each not working in App Flow, but works in Custom Query Builder

Not sure if I’m doing something wrong or if there’s a bug. I’m working on a custom query since Wappler’s Database Query Builder doesn’t support JSON functions (i.e. json_each). I am able to test the query in the Custom Query Builder, but when I add the App Flow to a page and run it, the query data is blank.

SELECT `FromName`, `Subject` FROM `Email`, json_each(`Email`.`GmailLabelIds`) WHERE json_each.value = ? ORDER BY `ReceivedDateTime` ASC;

This video shows the query builder returning records, but when I run the App Flow on a page, no results are returned.

@George any idea why this works in Query Builder, but I get "Error invoking remote method 'CapacitorSQLite-query': Error: Query: Error: SelectSQL: QueryAll: SQLITE_RANGE: column index out of range" in the app flow within Electron?

Within the browser I get "Query failed: SelectSQL: queryAll: undefined

Here’s the query

SELECT Subject, GmailLabelIds FROM Email, json_each(Email.GmailLabelIds) WHERE json_each.value = 'INBOX' ORDER BY ReceivedDateTime ASC;

If I remove the json_each, then the query works.

SELECT Subject, GmailLabelIds FROM Email ORDER BY ReceivedDateTime ASC;

Here’s the table with sample data and app flow if it helps.
Email Table.zip (936 Bytes) testflow.zip (697 Bytes)

If the query is exactly the same than the only think I can think of is that the parameters you passed are somehow empty in the runtime.

I hard coded the value, so no parameters at the moment.

Could there be a defect/limitation in dmxCapacitorSQLite.js that doesn’t allow json_each?

json_each is a very specific json extension implemented in SQLite so you have to check the version and support at their site and github.

The query works in Wappler’s query builder and using Beekeeper Studio against the SQLite db connection, that leads me to believe it’s something in capacitor or the dmx app connect adapter.

Bump

I checked your testflow file and it still contains the parameter while in the query you removed it. This causes the error, remove the parameter.

Thanks @patrick! Then I’m almost there, but I’m having trouble with the Parameters.

If I hardcode the json_each.value it’s now returning records

SELECT Subject, GmailLabelIds FROM Email, json_each(Email.GmailLabelIds) WHERE json_each.value = 'INBOX' ORDER BY ReceivedDateTime ASC;

image

If I attempt to use parameters it’s not working. I’ve tried using a param from the Page Flow ($param.label) that the database query is a a part of and also a query param (query.l). Neither are successful in returning the records.

SELECT Subject, GmailLabelIds FROM `Email`, json_each(`Email`.`GmailLabelIds`) WHERE json_each.value = ? ORDER BY `ReceivedDateTime` ASC;

image

I’m not sure if it’s only a UI issue, but neither the flow params or the query params show, so I have to manually enter them in the expression.

If I try a non-custom Database Query, then the Conditions bindings show the query parameters, but the Flow parameters are still missing. I need to use a custom query so I can use json_each.value, but just showing the different in bindings between the two query builders.