Sqlc Generation Error JSONB

[usamasaif]

Version

1.26.0

What happened?

I m facing issue on sqlc generation.
UPDATE order_books SET buy_orders = ( SELECT jsonb_agg( CASE WHEN (elem->>'price')::numeric = $2 AND (elem->>'quantity')::int - $3 > 0 THEN jsonb_set(elem, '{quantity}', to_jsonb(((elem->>'quantity')::int - $3)::int)) ELSE elem END ) FROM jsonb_array_elements(buy_orders) "elem" WHERE (elem->>'price')::numeric = $2 AND (elem->>'quantity')::int - $3 > 0 OR (elem->>'price')::numeric != $2 ) WHERE market_id = $1;
above query working fine in pgadmin postgresql but facing issue in sqlc that "elem" does not exist.

Relevant log output

# package db
internal/order_book/database/queries/order_book.sql:27:19: column "elem" does not exist

Database schema

CREATE TABLE "order_books" (
    "order_book_id" UUID PRIMARY KEY,
    "market_id" bigint REFERENCES markets(market_id),
    "buy_orders" JSONB,
    "sell_orders" JSONB,
    "created_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    "updated_at" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

SQL queries

-- name: DeleteBuyOrders :exec
UPDATE order_books
SET buy_orders = (
    SELECT jsonb_agg(
        CASE
            WHEN (elem->>'price')::numeric = $2 AND (elem->>'quantity')::int - $3 > 0 THEN
                jsonb_set(elem, '{quantity}', to_jsonb(((elem->>'quantity')::int - $3)::int))
            ELSE
                elem
        END
    )
    FROM jsonb_array_elements(buy_orders) "elem"
    WHERE 
        (elem->>'price')::numeric = $2 AND (elem->>'quantity')::int - $3 > 0
        OR (elem->>'price')::numeric != $2
)
WHERE market_id = $1;

Configuration

version: "2"
sql:
  - engine: "postgresql"
    queries: "./internal/markets/database/queries"
    schema: "./db/migrations/go_migrate"
    gen:
      go:
        package: "db"
        out: "./internal/markets/database/sqlc"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_prepared_queries: false
        emit_interface: true
        emit_exact_table_names: false
        emit_empty_slices: true
  - engine: "postgresql"
    queries: "./internal/orders/database/queries"
    schema: "./db/migrations/go_migrate"
    gen:
      go:
        package: "db"
        out: "./internal/orders/database/sqlc"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_prepared_queries: false
        emit_interface: true
        emit_exact_table_names: false
        emit_empty_slices: true
  - engine: "postgresql"
    queries: "./internal/order_book/database/queries"
    schema: "./db/migrations/go_migrate"
    gen:
      go:
        package: "db"
        out: "./internal/order_book/database/sqlc"
        sql_package: "pgx/v5"
        emit_json_tags: true
        emit_prepared_queries: false
        emit_interface: true
        emit_exact_table_names: false
        emit_empty_slices: true

Playground URL

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

What operating system are you using?

macOS

What database engines are you using?

PostgreSQL

What type of code are you generating?

Go