SQLite: running generate on a query using the json_each function results in 'column "value" does not exist' for json_each.value reference

[hopesea]

Version

1.28.0

What happened?

Referening the "value" column from json_each function results in the error message 'column "value" does not exist'.
Aliasing the result from json_each function call has no impact either.

Relevant log output

sqlc generate failed.
# package 
query.sql:4:7: 396: column "value" does not exist

Database schema

CREATE TABLE demo (
	id INTEGER PRIMARY KEY AUTOINCREMENT,
	simple_val TEXT,
	compound_json_val TEXT
);

SQL queries

-- name: FetchDemoRowsByJsonVal :many
SELECT demo.*
  FROM demo, json_each(demo.compound_json_val)
WHERE json_each.value IN (sqlc.slice('param'));

Configuration

{
  "version": "2",
  "sql": [{
    "schema": "schema.sql",
    "queries": "query.sql",
    "engine": "sqlite",
    "gen": {
      "go": {
        "out": "db"
      }
    }
  }]
}

Playground URL

https://play.sqlc.dev/p/b48066cc2ed3aaba5ed85e794b275c9d3b4857b70d0c810b95de28d2e1a0d303

What operating system are you using?

macOS

What database engines are you using?

SQLite

What type of code are you generating?

Go

[brandur]

I'm also running into this, and moreso finding that none of my tricks for doing a multi-insert in sqlc work when trying to map them into SQLite.

In Postgres I'd do something like this:

-- name: RiverMigrationInsertMany :many
INSERT INTO /* TEMPLATE: schema */river_migration (
    line,
    version
)
SELECT
    @line,
    unnest(@version::bigint[])
RETURNING *;

But in SQLite there's no unnest or arrays, so I was trying to use something like this instead:

-- name: RiverMigrationInsertMany :many
INSERT INTO /* TEMPLATE: schema */river_migration (
    line,
    version
)
SELECT
    @line,
    value
FROM 
    json_each(json_array(sqlc.slice('version')))
RETURNING *;

But getting the same error as above:

river_migration.sql:52:8: column "value" does not exist

sqlc.slice is somewhat useful, but mostly for use with IN. I don't think it can be used for inserts unless it can be used in addition to another helper like json_each.

@kyleconroy I still don't know the sqlc code base that well, but any idea what it'd take to get something like this working?