sqlite: `json_each`'s `value` parameter isn't supported

[jamietanna]

Version

1.15.0 - 1.23.0

What happened?

The noted query produces an error:

query/query.sql:72:1: column reference "value" not found
exit status 1
db/generate.go:3: running "go": exit status 1

When running go generate, with the following build tag:

//go:generate go run github.com/kyleconroy/sqlc/cmd/sqlc generate

When running this in an sqlite3 v3.38.5 shell, this produces:

slug                       raw                                                           key  value  type  atom  id  parent  fullkey  path  count(*)
-------------------------  ------------------------------------------------------------  ---  -----  ----  ----  --  ------  -------  ----  --------
/posts/2022/9/4/cXkdvjoj/  {"type":["h-entry"],"properties":{"category":["foo","bar"],"  1    bar    text  bar   2           $[1]     $     9       
                           published":["2022-09-04T20:17:13+01:00"],"updated":["2022-09                                                             
                           -04T20:17:13+01:00"],"visibility":["private"]}}                                                                          

For the following entry in the database:

select * from posts WHERE slug = '/posts/2022/9/4/cXkdvjoj/';
/posts/2022/9/4/cXkdvjoj/|{"type":["h-entry"],"properties":{"category":["foo","bar"],"published":["2022-09-04T20:17:13+01:00"],"updated":["2022-09-04T20:17:13+01:00"],"visibility":["private"]}}

Relevant log output

No response

Database schema

CREATE TABLE IF NOT EXISTS posts (
  slug text NOT NULL PRIMARY KEY,
  raw text NOT NULL -- raw is a JSON object
);

SQL queries

select
value, count(value)
  -- json_extract(posts.raw, '$.properties.category') category
  from posts, json_each(json_extract(posts.raw, '$.properties.category'))
  GROUP BY value
  ORDER BY count(value) DESC
  ;

Configuration

{
  "version": "1",
  "packages": [
    {
      "path": "sqlite",
      "name": "posts",
      "schema": "schema",
      "queries": "query",
      "engine": "sqlite",
      "emit_json_tags": true,
      "emit_prepared_queries": true,
      "emit_interface": true
    }
  ]
}

Playground URL

No response

What operating system are you using?

Linux

What database engines are you using?

No response

What type of code are you generating?

Go