search

fr-ser / grafana-sqlite-datasource

Grafana Plugin to enable SQLite as a Datasource
Apache License 2.0
124 stars 17 forks source link

no such function: json_extract #29

Closed Shorton88 closed 3 years ago

Shorton88 commented 3 years ago

My SQLite DB has a custom view which requires JSON_EXTRACT. It works fine in DB Browser for SQLite but throws the above error in grafana.

My Query for reference select datetime(ge.worldtime, 'unixepoch') as LocalHostTime, ge.eventType as EventType, ' X: ' || ge.x || ' Y: ' || ge.y || ' Z: ' || ge.z as IncidentArea, ge.ownerId as VictimPlayerId, ge.ownerName as VictimPlayerName, ge.ownerGuildId as VictimGuildID, guilds.name as VictimGuildName, ge.causerId as AttackingPlayerID, ge.causerName as AttackingPlayerName, ge.causerGuildId as AttackingGuildID, ge.causerGuildName as AttackingGuildName, json_extract(ge.argsMap, '$.itemTemplateId') as ItemID, cust_item_xref.name as ItemName, ge.stack as StackedAmount from game_events as ge INNER JOIN cust_item_xref ON cust_item_xref.template_id = ItemID INNER JOIN guilds ON guilds.guildId = VictimGuildID where ItemID != '' order by LocalHostTime DESC

Shorton88 commented 3 years ago

This was my misunderstanding. json_extract cannot be used from a query alone it needs to be able to use an in member table which is why it works in db browser. I've corrected this by created an automated task to create a table with same values and have grafana pull data from that.

fr-ser commented 3 years ago

Seems like the issue is fine now. I know that there appears to be some JSON functionality, that needs to be compiled into SQLite, where I am not sure if the plugin does it correctly now. So some JSON features might indeed be missing. But it looks like that is not your case...

clarkmcc commented 3 years ago

@fr-ser do you plan on releasing a version with support for JSON functionality? It looks like it can be enabled by default at compile time using a Go build tag.

go build -tags json1 github.com/mattn/go-sqlite3

Edit: I see this is solved in https://github.com/fr-ser/grafana-sqlite-datasource/issues/53