Repository navigation
Impossible to use SELECT with JSON_TO_RECORDSET #2191
Description
Version
1.17.2
What happened?
I would want to select a field from a JSON. It seams right now not possible.
I have a query like this:
WITH to_update as (
SELECT x
FROM JSON_TO_RECORDSET(@auth_users::JSON)
AS x("id" BIGINT, "label" VARCHAR(55))
)
UPDATE auth_users
SET label = to_update.label
FROM to_update
WHERE auth_users.id = to_update.id
AND auth_users.user_id = @user_id;I can simplify to show the error:
SELECT x
FROM JSON_TO_RECORDSET(@users::JSON)
AS x("id" BIGINT, "label" VARCHAR(55));Here `column "x" does not exist
If I try to use a select all:
SELECT *
FROM JSON_TO_RECORDSET(@users::JSON)
AS x("id" BIGINT, "label" VARCHAR(55));
-- or
SELECT x.*
FROM JSON_TO_RECORDSET(@users::JSON)
AS x("id" BIGINT, "label" VARCHAR(55));The output ignore it
SELECT
FROM JSON_TO_RECORDSET($1::JSON)
AS x("id" BIGINT, "label" VARCHAR(55))Has any one a workaround? :)
Relevant log output
query.sql:1:8: column "x" does not existDatabase schema
No response
SQL queries
No response
Configuration
No response
Playground URL
https://play.sqlc.dev/p/a3d9027cd2c77e50a70426c9a2d195960864fcb34c80482e30d29b8cc3d22d4f
What operating system are you using?
Linux
What database engines are you using?
PostgreSQL
What type of code are you generating?
Go
Activity
realdecisions commentedon Apr 13, 2023
Try to describe in select types for selected columns like select x.id::int8, x.label::varchar in subquery
In my case sqlc with unnest start working only after describing types explicitly for columns in select
intersting.. my subquery not working in playground, but on local machine with shema - all works . ver 1.17.2
Try localy
tcastelly commentedon Apr 14, 2023
Thx, it works for me!
This is my new query